Dynamic PivotTable

Write a perspective stored procedure that meets your requirements. During development, it can be used directly.

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-19
-- Update date: 2019-05-19
-- Description: Dynamic perspective
CREATE PROCEDURE  [dbo].[usp_Dynamic_Pivot]
(        
    @table_name SYSNAME,           --Table name of pivot
    @common_column SYSNAME,        --Common column name
    @which_row_to_column SYSNAME,  --Which row needs to be pivoted to the column name
    @sum_column SYSNAME            --Column name of sum
)    
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
Source Code

 

The stored procedure has several parameters:

    @Table name sysname, -- name of the pivot table
    @Common column sysname, -- common column name
    @Which row to column sysname, - which row needs to be pivoted to the column name
    @Sum column sysname -- column name of sum

 

For example, prepare some data first:

 

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
Source Code

 

Now, let's execute the above stored procedure and take a closer look at the parameters passed in:

 

Another example:

 

Turn to the previous example< T-SQL PIVOT row column conversion> https://www.cnblogs.com/insus/archive/2011/03/05/1971446.html 

Instead, use the above stored procedure to implement:

Tags: SQL Sever Stored Procedure SQL xml

Posted on Fri, 08 Nov 2019 11:32:17 -0500 by mxdan