我需要在表上编写一个sql查询,以便结果包含group by列和带有逗号分隔符的聚合列。
我的表格将采用以下格式
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a |
|_________|________|
| 1 | b |
|_________|________|
| 2 | c |
|_________|________|
预期结果应采用以下格式
|`````````|````````|
| ID | Value |
|_________|________|
| 1 | a,b |
|_________|________|
| 2 | c |
|_________|________|
发布于 2011-06-14 22:49:40
从一个平衡的角度来看,你也可以用CTE来做这件事,但我不认为它不如交叉应用方法好。我已经写好了这段代码,所以如果它不能工作,我深表歉意。
WITH CommaDelimitedCTE (RowNumber,ID,[Value],[Values]) AS
(
SELECT 1,MT.ID , MIN(MT.Value), CAST(MIN(MT.Value) AS VARCHAR(8000))
FROM MyTable MT
GROUP BY MT.ID
UNION ALL
SELECT CT.RowNumber + 1, MT.ID, MT.Value, CT.[Values] + ', ' + MT.Value
FROM MyTable MT
INNER JOIN CommaDelimitedCTE CT ON CT.ID = MT.ID
WHERE MT.[Value] > CT.[Value]
)
Select CommaDelimitedCTE.* from CommaDelimitedCTE
INNER JOIN (SELECT MT.ID,MAX(RowNumber) as MaxRowNumber from CommaDelimitedCTE GROUP BY MT.ID) Q on Q.MT.ID = CommaDelimitedCTE.MT.ID
AND Q.MaxRowNumber = CommaDelimitedCTE.RowNumber
https://stackoverflow.com/questions/6344950
复制相似问题