SQL 2005 行转列，列转行，未知行转列时使用动态sql

最新推荐文章于 2022-12-08 18:03:33 发布

原创最新推荐文章于 2022-12-08 18:03:33 发布 · 2.3k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #insert #table #pivot

MSSQL 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何在SQL Server 2005中实现行转列和列转行操作，包括静态和动态SQL的方法，并提供了具体的例子来展示这些转换技巧。

SQL 2005 行转列，列转行，未知行转列时使用动态sql。

Create TABLE salesByMonth ( year char(4), month char(3), amount money, PRIMARY KEY (year, month) )

Insert INTO salesByMonth (year, month, amount) VALUES('2004','Jan', 789.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Feb', 389.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Mar', 8867.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Apr', 778.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','May', 78.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Jun', 9.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Jul', 987.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Aug', 866.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Sep', 7787.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Oct', 85576.0000)
Insert INTO salesByMonth (year, month, amount) VALUES('2004','Nov', 855.0000)

select Year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov]
FROM salesByMonth
PIVOT ( sum(amount) FOR month IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov]) ) AS ourPivot ORDER BY Year

--动态sql写法
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + month from salesByMonth group by month
set @sql = '[' + @sql + ']'
exec ('select * from salesByMonth pivot (sum(amount) for month in (' + @sql + ')) AS ourPivot ORDER BY Year')

Create TABLE salesByMonth1 ( year char(4), Jan money,Feb money,Mar money,Apr money,
May money,Jun money,Jul money,Aug money,Sep money,Oct money,Nov money, amount money)

Insert INTO salesByMonth1 (year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov])
VALUES('2005',789.00,389.00,8867.00,778.00,78.00,9.00,987.00,866.00,7787.00,85576.00,855.00)
Insert INTO salesByMonth1 (year,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov])
VALUES('2005',734.00,389.00,84367.00,378.00,734.00,9.00,47.00,866.00,7487.00,8536.00,83.00)
select * from salesByMonth1

select Year,需要转的列名,需要转的列值
FROM salesByMonth1
UNPIVOT (需要转的列值 FOR 需要转的列名 IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov) ) AS ourPivot ORDER BY Year

--动态sql写法
declare @sql varchar(8000)
set @sql = 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov'
exec ('select Year,需要转的列名,需要转的列值 FROM salesByMonth1 UNPIVOT (需要转的列值 FOR 需要转的列名 IN ('
+ @sql + ')) AS ourPivot ORDER BY Year')