concatenate years

Question

SELECT DISTINCT
        PART ,
        MakeName ,
        ModelName ,
        YearID
FROM    COMPATMATRIX..PART_TABLE
        LEFT JOIN MATRIX_ACES(NOLOCK) ON PART_TABLE.MFGID = MATRIX_ACES.MFGID
                                         AND PART_TABLE.MFG_PART = MATRIX_ACES.MFG_PART
        LEFT JOIN ACES..BASEVEHICLE(NOLOCK) ON MATRIX_ACES.BaseVehicleID = BaseVehicle.BaseVehicleID
        LEFT JOIN ACES..Make(NOLOCK) ON BaseVehicle.MakeID = Make.MakeID
        LEFT JOIN ACES..Model(NOLOCK) ON BaseVehicle.ModelID = Model.ModelID
WHERE   PART_TABLE.MFGID IN ( 'ACC', 'DRT' )
        AND MakeName IS NOT NULL
ORDER BY PART ,
        MakeName ,
        ModelName ,
        YearID

I'm try to concatenate all the years in a single row. So there may be multiple Ford F-150's and the only thing that differs is the year and I would like all the years to be in one row instead of having each different year being a new row.

I have tried using GROUP BY but then I have to use an aggregate and that only selects one year. I'm a little stumped. I'm using SQL Server 2008.

sample of what currently happens

ACC1234   Ford    F-150   2001
ACC1234   Ford    F-150   2002
ACC1234   Dodge   Ram     2000

What I would like

ACC1234   Ford    F-150   2001, 2002
ACC1234   Dodge   Ram     2000

Show sample data and an expected result. It can be simplified. — Tim Schmelter, Aug 14 '15 at 12:58
Possible duplicate of [this one](http://stackoverflow.com/q/194852/419956), though impossible to tell without sample data and simplification down to an sscce. — Jeroen, Aug 14 '15 at 13:00
I think you want to [pivot the data](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server), but to do that you need to know how many years you'll be looking at, or you have to write dynamic SQL... alternatively, you could use [case statements](http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when). — xQbert, Aug 14 '15 at 13:01
Please stop placing `(NOLOCK)` everywhere. It really isn't a good idea — Mark Sinkinson, Aug 14 '15 at 13:12
To back up what @MarkSinkinson says with some details instead of just an opinion please see this article. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Aug 14 '15 at 13:28
Thanks for the link on the NOLOCK. I'm borrowing this script from someone else as my SQL is pretty weak so I had no idea it was bad behavior. — ckrausk, Aug 14 '15 at 13:45

score 0 · Answer 1 · edited May 23 '17 at 11:58

When I had to something similar, I ended up doing a dynamic SQL, but this was with Oracle (I guess it's possible to do the same in SQL Server based on this answer):

SELECT Truckname, 
       SUM(Year2013) Year2013, 
       SUM(Year2014) Year2014, 
       SUM(Year2015) Year2015 
FROM
(
  SELECT Truckname, NULL Year2013, NULL Year2014, COUNT(Field) Year2015
  FROM Trucks
  WHERE Year = '2015'
  UNION ALL
  SELECT Truckname, NULL Year2013, COUNT(Field) Year2014, NULL Year2015
  FROM Trucks
  WHERE Year = '2014'
  UNION ALL
  SELECT Truckname, COUNT(Field) Year2013, NULL Year2014, NULL Year2015
  FROM Trucks
  WHERE Year = '2013'
) 
GROUP BY Truckname

All the "union all selects" of the years are generated dynamically depending how many years you want. It's probably not the most elegant / optimized solution, but you have one row with the information of all the years. I you want text instead of a counter, you can use MAX instead of count to get the value.

I hope I understood correctly what you are trying to achieve here.

Paurian · Accepted Answer · 2015-08-14T13:31:17.770

0

Look into STUFF and FOR XML PATH.

http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

Taking your code as an example:

SELECT DISTINCT
        PART ,
        MakeName ,
        ModelName ,
        STUFF( ( SELECT ',' + CONVERT( VARCHAR(10), YearID ) FROM ... WHERE ... FOR XML PATH( '' ) ), 1, 1, '' ) AS YearIDs
:
:
ORDER BY PART ,
        MakeName ,
        ModelName

Because I don't know what table YearID comes from and don't know the primary keys, I couldn't build the FROM or WHERE clause for you, but I think this will get you on the right path.

Good luck!

edited Aug 14 '15 at 13:31

answered Aug 14 '15 at 13:24

Paurian

1,372
10
18

I had to make a few modifications but this put me on the right path. Thanks. – ckrausk Aug 14 '15 at 14:51

concatenate years

2 Answers2