Stored Procedure: Pivot With Dynamic Columns

Following is a stored procedure in aims to improve Microsoft built-in function of PIVOT in SQL Server. To use PIVOT, one must know exact number and name of columns after pivot, which does not meet the real world’s need. What we need is the pivot function works with dynamic pivot columns. To use this stored procedure, you would need to create a temporary table first with name of “Temp_Transform_Input”. Following is the structure of the this table:

Column 1, can be any name and data type. It would remain intact as it was after pivot.
Column 2, must be named as “Pivot_Column” and should be VARCHAR data type. Its contents after automatically distinct would be pivoted as column titles after execute this procedure.
Column 3, must be named as “Content_Column” and should be INT data type. It would be summed up after pivot. If the purpose is to count, put 1 here for every record.

To use it, just type EXECUTE P_Transform GO. The result will be automatically saved into 7 temporary tables. You do not need to manage the garbage collection for these two temporary tables, this procedure will manage them for you.

Why use temporary table instead of use Multi-statement Table-Valued Function as output method? Since you are dealing with the dynamic columns, it is impossible to declare the return table in Multi-statement Table-Valued Function.

CREATE PROCEDURE P_Transform
AS
BEGIN
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Largest'
)
DROP TABLE dbo.Temp_Transform_Row_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Sum'
)
DROP TABLE dbo.Temp_Transform_Row_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Largest'
)
DROP TABLE dbo.Temp_Transform_Column_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Sum'
)
DROP TABLE dbo.Temp_Transform_Column_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Largest'
)
DROP TABLE dbo.Temp_Transform_Both_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Sum'
)
DROP TABLE dbo.Temp_Transform_Both_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Result'
)
DROP TABLE dbo.Temp_Transform_Result
DECLARE @FirstColumn VARCHAR(128)
DECLARE @Summary VARCHAR(512)
DECLARE @Columns VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
SELECT @FirstColumn = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Input'
AND ORDINAL_POSITION = 1
SET @Summary =
'
SELECT ' + @FirstColumn + '
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Row_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY MAX(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, SUM(Content_Column) AS Total
INTO Temp_Transform_Row_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Column_Largest
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Column_Sum
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY SUM(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Both_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Both_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
, Pivot_Column
'
EXECUTE(@Summary)
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
(
SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query =
'
SELECT *
INTO Temp_Transform_Result
FROM
(
SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
(
SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Input'
)
DROP TABLE dbo.Temp_Transform_Input
END
GO

It is very important the length of each contents in Pivot_Column must be less 30 characters, because the contents will become the column name after transpose. Else, it won't work properly.


http://www.paladn.com/component/content/article/40-sql-server/122-udf-forms.html

Pivot Large Table With Dynamic Columns

In general, it is recommended to use SUM(CASE) GROUP BY method to pivot rows into columns. For details why recommended such, please refer to http://koncordpartners.blogspot.com/2010/03/pivot-turning-rows-into-columns.html. However, in two circumstances SUM(CASE) GROUP BY method does not work:

1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.

2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.

In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.

The PIVOT function is easy:

SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q

The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.

Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.

Step 1

CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO

Step 2

Prepare dynamic queries:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q

Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:

DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'

What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:

DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q

Then, execute one by one before union together.

Step 3

To execute the queries:

EXECUTE(@query)


http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html

PIVOT – Turning Rows into Columns

Here we exam various PIVOT functions/methods in MS Access, Oracle and SQL Server.

MS Access:

TRANSFORM does not work properly because it would generate many rows with a lot of zeros. Let us call it seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with IIF() does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

Oracle:

SELECT PIVOT does not work properly because of seggragation. However, covring it by a SUM() GROUP BY statement will do.

SELECT with CASE does work perfect. Example: http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html

OVER PARTITION BY does work fine, but not recommended because lack of flexibility. Example: http://baurdotnet.wordpress.com/2009/08/26/select/

SQL Server:

PIVOT does work fine, but strongly not recommended for two reasons: 1, Aggragate function whtin PIVOT can not be recalculated according to your will. 2, T-SQL does not allow ORDER BY working within the inner query, which does cause a lot of difficulties for layout arrangement.

A Practical Case of Transpose Query

Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.

Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:

1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:

SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id

ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.

2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:

SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id

Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.

3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:

SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id

4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:

SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Put everything together:

WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Done.