sqlserver中多行转成单行的综合总结。多行行分组单列分逗号隔开显示，行列转换等

1  有时需要在动态sql语句中拼接语句，需要行列转换动态化，需要生成横排的栏位.

示例如下。

declare @sql varchar(400)
DECLARE @T1  TABLE(ID INT,SIZE_VALUE VARCHAR(10),SIZENAME VARCHAR(10) )
insert into @T1
SELECT ID=1,SIZE_VALUE='A001',SIZENAME='330'
UNION
SELECT ID=2,SIZE_VALUE='A001',SIZENAME='335'
UNION
SELECT ID=3,SIZE_VALUE='A001',SIZENAME='340'
UNION
SELECT ID=4,SIZE_VALUE='A001',SIZENAME='345'
UNION
SELECT ID=5,SIZE_VALUE='A001',SIZENAME='350'
UNION
SELECT ID=6,SIZE_VALUE='A001',SIZENAME='355'

SELECT @sql=isnull(@sql+',','')+'['+A.SIZENAME+']' 
 FROM  @T1 A
GROUP BY A.ID,A.SIZENAME
SELECT (@SQL)

如下图

2 有时需要把多行数据，把某列的数据按逗号隔开

示例如下：

 DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));  
 insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')  
 insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')  
 insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')  
 insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')  
 insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')  
     
SELECT B.CityName, LEFT(UserList,LEN(UserList)-1)  
 FROM (  
      SELECT CityName,  
                    (SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName  FOR XML PATH('')) AS UserList 
     FROM @T1 A   
     GROUP BY CityName  
) B 

如下图

 3 使用PIVOT把行转成列（竖排转成横排)，

示例如下：

SELECT *
INTO #T1
FROM 
(
    SELECT ID=1,SIZE_VALUE='A001',SIZENAME='330',qty=5
    UNION
    SELECT ID=2,SIZE_VALUE='A001',SIZENAME='335',qty=4
    UNION
    SELECT ID=3,SIZE_VALUE='A001',SIZENAME='340',qty=2
    UNION
    SELECT ID=4,SIZE_VALUE='A001',SIZENAME='345',qty=2
    UNION
    SELECT ID=5,SIZE_VALUE='A001',SIZENAME='350',qty=2
    UNION
    SELECT ID=6,SIZE_VALUE='A001',SIZENAME='355',qty=3
) T

select SIZE_VALUE, [330],[335],[340],[345],[350],[355]
from ( select size_value,sizename,sum(qty) qty from #t1 group by size_value,sizename  ) a
PIVOT
(
   SUM(QTY) FOR [SIZENAME] IN ([330],[335],[340],[345],[350],[355])
) AS total order by SIZE_VALUE

如下图

 

4 使用UNPIVOT把列转成行，横排转竖排，

示例如下，接竖转横数据。

把上面横排数据放入临时表

select SIZE_VALUE, [330],[335],[340],[345],[350],[355]
into #t2
from ( select size_value,sizename,sum(qty) qty from #t1 group by size_value,sizename  ) a
PIVOT
(
   SUM(QTY) FOR [SIZENAME] IN ([330],[335],[340],[345],[350],[355])
) AS total order by SIZE_VALUE    

   select *
    from  #T2
    unpivot( QTY for size_name in([330],[335],[340],[345],[350],[355]) ) as upvttable 

如下图所示