T-SQL PIVOT 行列转换

写一个符合自己要求使用透视存储过程。在开发时，直接使用即可。

 

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- create date: 2019-05-19
-- update date: 2019-05-19
-- description: 动态透视
create procedure  [dbo].[usp_dynamic_pivot]
(        
    @table_name sysname,           --透视的表名
    @common_column sysname,        --常规共用列名
    @which_row_to_column sysname,  --哪一行需要透视为列的列名
    @sum_column sysname            --计和的列名
)    
as
begin
declare @comma_delimited_column_names nvarchar(max)
declare @query nvarchar(max) = n'set @comma_delimited_column_names = stuff((select distinct '','' + quotename('+ @which_row_to_column +') 
        from '+ @table_name +' 
        for xml path(''''), type
        ).value(''.'', ''nvarchar(max)'') 
    ,1,1,'''')'

execute sp_executesql @query, n'@comma_delimited_column_names as nvarchar(max) output',@comma_delimited_column_names output
        
declare @sql as nvarchar(max) = n'
            select '+ @common_column +',            
                ' + @comma_delimited_column_names + ' 
            from 
            (
                select '+ @common_column +','+ @which_row_to_column +','+ @sum_column +' from '+ @table_name +'
            ) as [source]
            pivot 
            (
                sum('+ @sum_column +')
                for '+ @which_row_to_column +' in (' + @comma_delimited_column_names + ')
            ) as [pivot table] '
                

execute sp_executesql @sql

end
go

 

存储过程有几个参数：

    @table_name sysname,           --透视的表名
    @common_column sysname,        --常规共用列名
    @which_row_to_column sysname,  --哪一行需要透视为列的列名
    @sum_column sysname            --计和的列名

 

举例说明，先准备一些数据：

 

if object_id('tempdb.dbo.#part') is not null drop table #part
create table #part (
    [id] int,
    [item] nvarchar(40),
    [category] nvarchar(25),
    [qty] decimal(18,2)
)
go
insert into #part ([id],[item],[category],[qty]) values (23394,'i32-gg443-qt0098-0001','s',423.65),
                                                        (45008,'i38-aa321-ws0098-0506','b',470.87),
                                                        (14350,'k38-12321-5456ud-3493','b',200.28),
                                                        (64582,'872-rtde3-q459pw-2323','t',452.44),
                                                        (23545,'098-ssss1-ws0098-5526','s',500.00),
                                                        (80075,'b78-f1h2y-5456ud-2530','t',115.06),
                                                        (53567,'po0-7g7g7-jjy098-0077','q',871.33),
                                                        (44349,'54f-art43-6545nn-2514','s',934.39),
                                                        (36574,'x3c-sdewe-3er808-8764','q',607.88),
                                                        (36574,'rvc-43ase-h43qww-9753','u',555.19)

go

 

现在，我们执行上面的存储过程，仔细看所传入的参数：

 

另一个例子：

 

再翻开以前一个例子，《t-sql pivot 行列转换》

改为使用上面的存储过程来实现：