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

下麵是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) 

以下內容於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

查看最後的例子，以前是手動指定動態列轉為行的，現在已經不需要了。