我试着连续多年连接起来。 因此可能会有多辆福特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 , YearIDI'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 2000What 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 , ModelNameBecause 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!
更多推荐
发布评论