1 IF OBJECT_ID(‘TEMPDB..#TEMP‘)>0 2 DROP TABLE #TEMP 3 GO 4 IF OBJECT_ID(‘TEMPDB..#TEMP2‘)>0 5 DROP TABLE #TEMP2 6 GO 7  8 --模拟数据 9 SELECT *INTO #TEMP FROM (10 SELECT ‘A‘ col1,‘B‘ col2,‘C‘ col3,‘1‘ X,‘2‘Y,‘3‘Z11 UNION ALL12 SELECT ‘A‘ A,‘B‘ B,‘C‘ C,‘4‘ X,‘5‘Y,‘6‘Z13 UNION ALL14 SELECT ‘A‘ A,‘B‘ B,‘C‘ C,‘7‘ X,‘8‘Y,‘9‘Z15 UNION ALL16 SELECT ‘D‘ A,‘E‘ B,‘F‘ C,‘1‘ X,‘2‘Y,‘3‘Z17 UNION ALL18 SELECT ‘D‘ A,‘E‘ B,‘F‘ C,‘4‘ X,‘5‘Y,‘6‘Z19 UNION ALL20 SELECT ‘D‘ A,‘E‘ B,‘F‘ C,‘7‘ X,‘8‘Y,‘9‘Z21 UNION ALL22 SELECT ‘G‘ A,‘H‘ B,‘I‘ C,‘1‘ X,‘2‘Y,‘3‘Z23 UNION ALL24 SELECT ‘G‘ A,‘H‘ B,‘I‘ C,‘4‘ X,‘5‘Y,‘6‘Z25 UNION ALL26 SELECT ‘G‘ A,‘H‘ B,‘I‘ C,‘7‘ X,‘8‘Y,‘9‘Z)USB27 SELECT *FROM #TEMP28 --分组合并29 SELECT USB.col1,USB.col2,USB.col330 ,LEFT(xxx,LEN(XXX)-1)‘XXX‘31 INTO #TEMP232 FROM (33 SELECT col1,col2,col334 ,(SELECT X+‘,‘+Y+‘,‘+Z+‘,‘ FROM #TEMP T1 35 WHERE T1.col1=T2.col136 AND t1.col2=t2.col237 AND t1.col3=t2.col338 FOR XML PATH(‘‘))AS ‘xxx‘39 FROM #TEMP T240 GROUP BY T2.col1,T2.col2,T2.col3)USB41 SELECT * FROM #TEMP242 --分列处理43 DECLARE @SQL NVARCHAR(2000),@I INT44 SET @I=145 WHILE EXISTS(SELECT 1 FROM #TEMP2 WHERE XXX<>‘‘)46 BEGIN47 SET @SQL=‘ALTER TABLE #TEMP2 ADD 字段‘+CONVERT(VARCHAR,@I)+‘ VARCHAR(20)‘48 EXEC(@SQL)49 SET @SQL=‘declare @loc int update #temp2 set @loc=charindex(‘‘,‘‘,xxx),字段‘50     +convert(varchar,@i)+‘=convert(int,case @loc when 0 then xxx else ‘51     +‘substring(xxx,1,@loc-1) end),xxx=case @loc when 0 then ‘‘‘‘ else ‘52     +‘substring(xxx,@loc+1,len(xxx)-@loc) end  where xxx<>‘‘‘‘‘53     EXEC(@SQL)54     SET @I=@I+155 END56 ALTER TABLE #TEMP2 DROP COLUMN XXX57 --结果58 SELECT *FROM #TEMP2