SQL行列转换

最新推荐文章于 2024-07-23 15:38:58 发布

猫桐君

最新推荐文章于 2024-07-23 15:38:58 发布

阅读量113

点赞数

文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/qq_37526689/article/details/124860230

版权

本文对比了动态列生成与静态列定义的SQL查询，通过示例展示了如何使用ROW_NUMBER()函数动态生成列名，以及静态列在固定列数场景下的效率。涵盖了SQL查询、分区与排序，适合理解动态列在大数据处理中的应用。

摘要由CSDN通过智能技术生成

动态列
declare @sql1 varchar(8000)

set @sql1='select ProductId as ''型号'''
select @sql1=@sql1+',max(case d.id1 when '''+c.id1+''' then d.Price else 0 end) as ['+'列'+id1+']'
from (select cast(id1 as varchar(1000)) as id1 from (select a.*,id1=ROW_NUMBER()
OVER(partition by ProductId order by KeyinDate desc) from ZK_SAL_ModelPrice a) b group by id1) as c select @sql1=@sql1
+'from (select a.*,id1=ROW_NUMBER() over(partition by ProductId order by KeyinDate desc) from ZK_SAL_ModelPrice a)d group by d.ProductId'

exec (@sql1)

静态列

select ProductId as 'ProductId'
,max(case d.id1 when '1' then d.Price else 0 end) as [col1]
,max(case d.id1 when '2' then d.Price else 0 end) as [col2]
,max(case d.id1 when '3' then d.Price else 0 end) as [col3]
,max(case d.id1 when '4' then d.Price else 0 end) as [col4]
,max(case d.id1 when '5' then d.Price else 0 end) as [col5]
,max(case d.id1 when '6' then d.Price else 0 end) as [col6]
,max(case d.id1 when '7' then d.Price else 0 end) as [col7]
,max(case d.id1 when '8' then d.Price else 0 end) as [col8]
,max(case d.id1 when '9' then d.Price else 0 end) as [col9]
,max(case d.id1 when '10' then d.Price else 0 end) as [col10]
INTO #table1
FROM (select a.*,id1=ROW_NUMBER() over(partition by ProductId order by KeyinDate desc) from ZK_SAL_ModelPrice a)d
GROUP by d.ProductId