动态透视表

前几天一直练习pivot透视，还实现了动态透视的存过程《动态透视表》

今天练习ms sql server逆透视的功能。

首先准备一些可以逆透视的数据：

 

if object_id('tempdb.dbo.#part_summary') is not null drop table #part_summary

create table #part_summary (    
    [item] nvarchar(40),
    [b]decimal(18,2),
    [q]decimal(18,2),
    [s]decimal(18,2),
    [t]decimal(18,2),
    [u]decimal(18,2)
)

insert into #part_summary ([item],[b],[q],[s],[t],[u])
values ('098-ssss1-ws0098-5526',0,0,500.00,0,0),
       ('54f-art43-6545nn-2514',0,0,934.39,0,0),
       ('872-rtde3-q459pw-2323',0,0,0,452.44,0),
       ('b78-f1h2y-5456ud-2530',0,0,0,115.06,0),
       ('i32-gg443-qt0098-0001',0,0,423.65,0,0),
       ('i38-aa321-ws0098-0506',470.87,0,0,0,0),
       ('k38-12321-5456ud-3493',200.28,0,0,0,0),
       ('po0-7g7g7-jjy098-0077',0,871.33,0,0,0),
       ('rvc-43ase-h43qww-9753',0,0,0,0,555.19),
       ('x3c-sdewe-3er808-8764',0,607.88,0,0,0)

select [item],[b],[q],[s],[t],[u] from #part_summary

 

下面是insus.net实现unpivot的代码：

 

select [item]
      ,[category]
      ,[qty]
from #part_summary
unpivot (
          [qty] for [category] 
          in ([b],[q],[s],[t],[u]) 
    ) as [unpivot table]
where [qty] > 0

 

以上是以手动实现逆透视。如果想实现动态逆透视。可以参考下面的存储过程：

 

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- create date: 2019-05-20
-- update date: 2019-05-20
-- description: 动态逆透视
create procedure  [dbo].[usp_dynamic_unpivot]
(        
    @table_name sysname,           --透视的表名
    @common_column sysname,        --常规共用列名
    @unpivot_column sysname,       --逆透视列名
    @sum_column sysname,           --计和的列名
    @comma_delimited_column_names nvarchar(max) 
)    
as
begin        
declare @sql as nvarchar(max) = n'
select '+ @common_column +'
      ,'+ @unpivot_column +'
      ,[qty]
from '+ @table_name +'
unpivot (
          '+ @sum_column +' for '+ @unpivot_column +'
          in ('+ @comma_delimited_column_names +') 
        ) as [unpivot table]
where '+ @sum_column +' > 0'
                

execute sp_executesql @sql

end
go

 

怎样使用这个存储过程呢，参考下面参数传入方法：

 

重复一下，存储过程的参数说明如下：

@table_name sysname,           --透视的表名
@common_column sysname,        --常规共用列名
@unpivot_column sysname,       --逆透视列名
@sum_column sysname,           --计和的列名
@comma_delimited_column_names nvarchar(max)