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