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

最新推荐文章于 2022-10-08 09:30:21 发布

原创最新推荐文章于 2022-10-08 09:30:21 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

SQL Server数据库技术专栏收录该内容

44 篇文章

订阅专栏

本文介绍SQL中数据变形的四种实用技巧：动态SQL生成横排栏位、多行数据按列合并、使用PIVOT行转列及UNPIVOT列转行，帮助提升数据处理效率。

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

如下图所示