连接多年(concatenate years)

连接多年(concatenate years) 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

我试着连续多年连接起来。 因此可能会有多辆福特F-150并且唯一不同的是年份，我希望所有年份都是一排，而不是让每一年都成为新的一排。

我尝试过使用GROUP BY，但后来我必须使用聚合，而且只选择一年。 我有点难过。 我正在使用SQL Server 2008。

目前正在发生的样本

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

我想要什么

ACC1234 Ford F-150 2001, 2002 ACC1234 Dodge Ram 2000 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

最满意答案

查看STUFF和FOR XML PATH。

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

以您的代码为例：

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

因为我不知道YearID来自哪个表并且不知道主键，所以我无法为您构建FROM或WHERE子句，但我认为这将使您走上正确的道路。

祝你好运！

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!