一道sql 列转行的实现
sqlserver

QQ截图20201013105127.png在Sql Sever下,可以使用With AS构造数据,使用Pivot实现转换,
具体sql如下:

---构造测试数据
With test as (
Select 1991 as [year], 1 as [month], 1.1 as [amount]
union all
Select 1991 as [year], 2 as [month], 1.2 as [amount]
union all
Select 1991 as [year], 3 as [month], 1.3 as [amount]
union all
Select 1991 as [year], 4 as [month], 1.4 as [amount]
union all
Select 1992 as [year], 1 as [month], 2.1 as [amount]
union all
Select 1992 as [year], 2 as [month], 2.2 as [amount]
union all
Select 1992 as [year], 3 as [month], 2.3 as [amount]
union all
Select 1992 as [year], 4 as [month], 2.4 as [amount]
)
--原数据查询
--select * from test
--转换后查询
select [year],[1] as [m1],[2] as [m2],[3] as [m3], [4] as [m4] from test
pivot(
  max(amount) for [month] in ([1],[2],[3],[4])
) pt




暂无评论