UNPIVOT逆透视以及动态逆透视存储过程

前几天一直练习PIVOT透视，还实现了动态透视的存过程《动态透视表》https://www.cnblogs.com/insus/p/10888277.html

今天练习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

Source Code

下面是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

Source Code

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

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

Source Code

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

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

@table_name SYSNAME,           --透视的表名

@common_column SYSNAME,        --常规共用列名

@unpivot_column SYSNAME,       --逆透视列名

@sum_column SYSNAME,           --计和的列名

@Comma_Delimited_Column_Names NVARCHAR(MAX) 

以下内容于2019-05-22 09:02:02.610更新：

先参考这篇《数据表列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10848578.html

对上面的存储过程[dbo].[usp_Dynamic_UnPivot]去掉一个参数，把这个参数移至存储过程内部进行。

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:      Insus.NET

-- Create date: 2019-05-20

-- Update date: 2019-05-22

-- Description: 动态逆透视

CREATE PROCEDURE  [dbo].[usp_Dynamic_UnPivot]

(

    @table_name SYSNAME,           --透视的表名

    @common_column SYSNAME,        --常规共用列名

    @unpivot_column SYSNAME,       --逆透视列名

    @sum_column SYSNAME           --计和的列名

)

AS

BEGIN

    IF OBJECT_ID('tempdb.dbo.#table_structure') IS NOT NULL DROP TABLE #table_structure

    CREATE TABLE #table_structure([name] SYSNAME)

    INSERT INTO  #table_structure([name]) EXECUTE [dbo].[usp_Retrieve_Column_Name] 'tempdb','dbo',@table_name

    DELETE FROM #table_structure WHERE [name] = @common_column

    DECLARE @Comma_Delimited_Column_Names NVARCHAR(MAX)

    EXECUTE [dbo].[usp_TableColumnValueToCommaDelimitedString] '#table_structure','name',@Comma_Delimited_Column_Names OUTPUT

    DECLARE @sql AS NVARCHAR(MAX) = N'

    SELECT '+ @common_column +'

          ,'+ @unpivot_column +'

          ,'+ @sum_column +'

    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

Source Code

查看最后的例子，以前是手动指定动态列转为行的，现在已经不需要了。