分组合并，动态分列

IF OBJECT_ID('TEMPDB..#TEMP')IS NOT NULL
DROP TABLE #TEMP
GO
IF OBJECT_ID('TEMPDB..#TEMP2')IS NOT NULL
DROP TABLE #TEMP2
GO

--模拟数据
SELECT *INTO #TEMP FROM (
SELECT 'A' col1,'B' col2,'C' col3,'1' X,'2'Y,'3'Z
UNION ALL
SELECT 'A' A,'B' B,'C' C,'4' X,'5'Y,'6'Z
UNION ALL
SELECT 'A' A,'B' B,'C' C,'7' X,'8'Y,'9'Z
UNION ALL
SELECT 'D' A,'E' B,'F' C,'1' X,'2'Y,'3'Z
UNION ALL
SELECT 'D' A,'E' B,'F' C,'4' X,'5'Y,'6'Z
UNION ALL
SELECT 'D' A,'E' B,'F' C,'7' X,'8'Y,'9'Z
UNION ALL
SELECT 'G' A,'H' B,'I' C,'1' X,'2'Y,'3'Z
UNION ALL
SELECT 'G' A,'H' B,'I' C,'4' X,'5'Y,'6'Z
UNION ALL
SELECT 'G' A,'H' B,'I' C,'7' X,'8'Y,'9'Z)USB
SELECT *FROM #TEMP
--分组合并
SELECT USB.col1,USB.col2,USB.col3
,LEFT(xxx,LEN(XXX)-1)'XXX'
INTO #TEMP2
FROM (
SELECT col1,col2,col3
,(SELECT X+','+Y+','+Z+',' FROM #TEMP T1 
WHERE T1.col1=T2.col1
AND t1.col2=t2.col2
AND t1.col3=t2.col3
FOR XML PATH(''))AS 'xxx'
FROM #TEMP T2
GROUP BY T2.col1,T2.col2,T2.col3)USB
SELECT * FROM #TEMP2
--分列处理
DECLARE @SQL NVARCHAR(2000),@I INT
SET @I=1
WHILE EXISTS(SELECT 1 FROM #TEMP2 WHERE XXX<>'')
BEGIN
SET @SQL='ALTER TABLE #TEMP2 ADD 字段'+CONVERT(VARCHAR,@I)+' VARCHAR(20)'
EXEC(@SQL)
SET @SQL='declare @loc int update #temp2 set @loc=charindex('','',xxx),字段'
    +convert(varchar,@i)+'=convert(int,case @loc when 0 then xxx else '
    +'substring(xxx,1,@loc-1) end),xxx=case @loc when 0 then '''' else '
    +'substring(xxx,@loc+1,len(xxx)-@loc) end  where xxx<>'''''
    EXEC(@SQL)
    SET @I=@I+1
END
ALTER TABLE #TEMP2 DROP COLUMN XXX
--结果
SELECT *FROM #TEMP2

 

 

效果图预览：