Pivoting Data using T-SQL and SSIS Pivot Transformation

Pivot Data Using T-SQL

Microsoft has introduced the Pivot and Unpivot relational operators in T-SQL with the release of MS SQL Server 2005. The Pivot operator is used to transform and rotate data from a single column in to multiple columns (row to columns) in SQL.

The following are the queries used to create a sample table, insert some sample records and at last the Pivot query to transform the data from row to columns.

-- Create a sample table
CREATE TABLE [Pivot_Test](
    [Customer] [nchar](10) NOT NULL,
    [Product] [nchar](10) NOT NULL,
    [Quantity] [smallint] NOT NULL
)
-- Insert some sample records
INSERT INTO [Pivot_Test]
    ([Customer]
    ,[Product]
    ,[Quantity])
VALUES
    ('Cust A', 'Pepsi', 1),
    ('Cust B', 'Coke', 1),
    ('Cust C', 'Cake', 2),
    ('Cust A', 'Burger', 2),
    ('Cust B', 'Burger', 1),
    ('Cust C', 'Burger', 2),
    ('Cust C', 'Coke', 1)

The records have been loaded into the table. The screenshot is as below,

Before creating the query, user needs to know about the columns that have to be pivoted.

-- SQL query to pivot the data in the sample table    
SELECT  [Customer],
        [Pepsi],
        [Coke],
        [Cake],
        [Burger]
FROM    (SELECT [Customer],
                [Product],
                [Quantity]
        FROM    [R_n_D].[dbo].[Pivot_Test]) Src
PIVOT    (SUM([Quantity]) FOR [Product] IN([Pepsi],[Coke],[Cake],[Burger])) Pvt
ORDER BY [Customer]

Output for the above query,

Pivot Data Using SSIS Pivot Transformation

The above said Pivot action can be done also by using SSIS Pivot Transformation. One has to pay little bit of attention in configuring the Pivot Transformation.  The followings are the step by step to perform Pivot against data using the Transformation.

Step 1:
Create a simple package having OLE DB data source, Pivot Transformation and Excel Destination. As per the SSIS best practice use SQL query with the necessary fields in it. Don’t forget to have Order by clause in the SQL query. When I came across some of the blogs, they have advised to use the built-in sort function to those necessary field in the OLEDB data source by using the advance editor option (Input and Output Properties > OLE DB Source Output: IsSorted – True and mark 1,2.. as per the order of sort to the fields in the Output Column). Always prefer to use Order by clause while using Pivot transformation.

 

Step 2:
Open the Pivot Transformation and go to Input Columns tab; since we select the necessary fields in OLE DB datasource, select all the columns in the Input column tab. Go to Input and Output Properties tab, here we are going to configure how each column is being used in the Pivot operation.

Go to Pivot Default Input > Input Columns and select the relevant Input Column and set the PivotUsage property which is found under the custom header.

The following are the values that can be set in the PivotUsage property,

• 0 – column that is just passed through without any changes
• 1 – column that is a set key.  All input rows with the same set key are combined into one output row.
• 2 – column to pivot.
• 3 – values from these columns are placed in pivot columns.

Set 1 – Customer,

Set 2 – Products,

Set 3 – Quantity.

Step 3:
Initially there won’t be any output columns under the Pivot Default Output > Output Columns. Manually we have to create the output columns. Create an output column for Customer; change the source column property to match the LineageID from the input Customer column.

For the Product values, but here we need to enter the value of the product in the PivotKeyValue property. The product LineageID has to be updated in the Source column property. Repeat the same for the other products.

Step 4:
Create Excel destination and run the package.

The data is pivoted now