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

Excel File as Email Attachment for Reporting Services

The scenario is data are pulled out from SQL Server through a rather complicated query without use temporary table, and then copy the dataset to Excel. There are some presentation formats needed in Excel. This setting is the result from requirement since the report readers want it in Excel with live data.

First, using SQL Server Management Studio to arrange SQL Server side work, though it is not necessary. Create a SQL file, organize the SQL query, make sure no temporary table used, and the result should be a single dataset. Complicated formatting as well as query logic should be arranged within a single query by using inner queries as well as WITH AS statement.

If one dimension of original dataset from SQL Server is dynamic, make sure it would be the records dimension (vertical) rather than field dimension (horizontal). This is particularly important because if you have a dynamic horizontal dimension, any change would need human intervention by changing SELECT CASE part in query. You would need to make it as automation as possible. For details how this is work, please refer to http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html. If the dynamic dimension is to be at vertical direction in final report, you can write a special stored procedure to do the transpose transformation, or in Excel, using its built function. By adding CREATE VIEW AS, create a view which sits inside of SQL Server. SQL Server side work has been done.

In Excel, organize the presentation worksheets and dedicate a special worksheet for internal use. All live data including report covering period and report generating date time are to be organized in this internal sheet. Next step is to arrange the automation get external data directly from the view in SQL Server. Click Data in menu, Get External Data, From Other Sources, From Data Connection Wizard, Other/Advanced, A popup window will show. In Provider tab, select SQL Server Native Client 10.0, Next, make a right selection in Select or enter a server name, click Use Windows NT Integrated security, make a right selection in Select the database, then click OK. Finish it. Make sure the dataset from SQL Server will be arrived at right position of that internal worksheet.

Next is to create a button and write a VBA script to complete following tasks:
1. Make a transpose copy of dataset within the internal sheet, if it is necessary.
2. Copy whole or partial dataset to presentation worksheet by using Paste Values. Make sure there is no function in presentation worksheet at all. This does not only show internal works to the end users, but also make readers easily use the data as static.
3. Copy header and footer dynamic data to presentation worksheet by using Paste Values. Make sure this part is also of static data.
4. Hide the internal worksheet. If it is necessary you can also protect the hiden sheet by set password. All you may wish to completely delete the internal worksheet.
5. Automatically send email and attach this Excel file.

VBA Example:

Sub Process()
Sheets("Internal Use").Select
ActiveWorkbook.RefreshAll

Range("A5:AW68").Select
Selection.Copy
Range("B70").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True

Range("D1").Select
Selection.Copy
Sheets("Totals").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

Sheets("Internal Use").Select
Range("A3").Select
ActiveWindow.SelectedSheets.Visible = False

ActiveWorkbook.SendMail
Recipients:="someone@somewhere.com", _
Subject:="Weekly Report "
End Sub

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.