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 行列转换》
改为使用上面的存储过程来实现:
上一篇: PHP 是如何做垃圾回收的