利用行转列动态生产货架图

最新推荐文章于 2025-12-05 11:33:17 发布

原创最新推荐文章于 2025-12-05 11:33:17 发布 · 647 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #pivot #insert #table #c

Ms SQL Server 专栏收录该内容

115 篇文章

订阅专栏

本文介绍了一种使用SQL创建货架布局的方法，并演示了如何通过静态和动态Pivot查询将原始布局转换为更易于分析的形式。该过程首先定义了一个包含不同货架位置的数据表，接着通过Pivot操作实现了数据的宽表展示。

1、设置货架

create table WhInfo(R varchar(2),C varchar(2))
insert into WhInfo
select '01','01' union all
select '01','02' union all
select '01','03' union all
select '01','04' union all
select '01','05' union all
select '01','06' union all
select '01','07' union all
select '01','08' union all

select '02','01' union all
select '02','02' union all
select '02','03' union all
select '02','04' union all
select '02','05' union all
select '02','06' union all
select '02','07' union all
select '02','08' union all

select '03','01' union all
select '03','02' union all
select '03','03' union all
select '03','04' union all
select '03','05' union all
select '03','06' union all
select '03','07' union all
select '03','08' union all

select '04','01' union all
select '04','02' union all
select '04','03' union all
select '04','04' union all
select '04','05' union all
select '04','06' union all
select '04','07' union all
select '04','08'

２、静态转换

select * from (select * from WhInfo) a pivot (min(c) for C in ([01],[02],[03],[04],[05],[06],[07],[08],[09])) b

3、动态转换

declare @sql varchar(3000)
set @sql=''
select @sql=@sql+'['+c+'],' from WhInfo group by c
set @sql=left(@sql,len(@sql)-1)
print @sql
exec('select * from (select * from WhInfo) a pivot (min(c) for C in ('+@sql+')) b')