TA貢獻(xiàn)1877條經(jīng)驗(yàn) 獲得超6個(gè)贊
FOR XML PATH
select distinct t1.FieldA,
STUFF((SELECT distinct '' + t2.FieldB from yourtable t2 where t1.FieldA = t2.FieldA
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
,1,0,'') datafrom yourtable t1;
TA貢獻(xiàn)1826條經(jīng)驗(yàn) 獲得超6個(gè)贊
SELECT FieldA , GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
SELECT FieldA , LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
SELECT FieldA , STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
SQLServer≥2017&Azure SQL
SELECT FieldA , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
WITH CTE_TableName AS (
SELECT FieldA, FieldB FROM TableName)SELECT t0.FieldA , STUFF((
SELECT ',' + t1.FieldB FROM CTE_TableName t1 WHERE t1.FieldA = t0.FieldA
ORDER BY t1.FieldB FOR XML PATH('')), 1, LEN(','), '')
AS FieldBs FROM CTE_TableName t0 GROUP BY t0.FieldA ORDER BY FieldA;排序需要CTE或子查詢
WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName ORDER BY FieldA, FieldB)SELECT FieldA , GROUP_CONCAT(FieldB, ',') AS FieldBs FROM CTE_TableName GROUP BY FieldA ORDER BY FieldA;
不訂貨
SELECT FieldA , GROUP_CONCAT(FieldB, ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
舉報(bào)