欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

T-SQL PIVOT 行列转换

程序员文章站 2022-05-18 23:03:05
写一个符合自己要求使用透视存储过程。在开发时,直接使用即可。 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- -- Author: Insus.NET -- Create date: 2019-05-19 -- Update date: 201 ......

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

 

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 行列转换

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