Pivot rows to columns with the SSIS Pivot transformation

Step by step tutorial to transform rows into columns in an Integration Services dataflow using the SSIS Pivot transformation.

Integration Services natively allows you to pivot rows in a SQL Server table or column file with the SSIS Pivot component. But how to perform the operation in practice with an SSIS package? Here is a step-by-step tutorial on how to set up the Pivot SSIS component and turn the rows of a SQL table into columns, in our example one target column per month of the year. This simple example uses a single un-rotated column as the component does not allow you to rotate more than one column without using the advanced editor and SSIS internal column numbers, called LineageID.

Data pivoting is commonly used in pivot tables, for example. This step by step Integration Services tutorial explains how to pivot a basic SQL table from a vertical row structure to a horizontal column structure.

1. Create the source table with months stored in rows

First, create the 2 tables used, a source table in rows and the destination table in columns. In effect, create the sales table with the monthly values in rows.

CREATE TABLE [dbo].[Sales_Monthly_In_Lines]
(
  [YearID]      nvarchar(20) NOT NULL,
  [MonthName]   nvarchar(12) NOT NULL,
  [SalesAmount] numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyLinesPK] 
    PRIMARY KEY CLUSTERED (
      [YearID], [MonthName] ASC
    )
);
GO

2. Insert the monthly dataset

Insert a dataset to fill the table with 2 years of data, i.e. from the January 2021 to December 2022.

INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'01_January', 221.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'02_February', 2658.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'03_March', 2485.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'04_April', 6302.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'05_May', 7605.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'06_June', 5288.50 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'07_July', 3335.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'08_August', 5216.40 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'09_September', 4372.20 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'10_October', 9815.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'11_November', 5479.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2021', N'12_December', 9212.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'01_January', 7575.90 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'02_February', 6662.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'03_March', 1464.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'04_April', 7011.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'05_May', 3978.30 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'06_June', 4101.10 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'07_July', 5964.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'08_August', 2619.60 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'09_September', 4482.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'10_October', 3874.00 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'11_November', 3171.80 );
INSERT [dbo].[Sales_Monthly_In_Lines] VALUES (N'2022', N'12_December', 1610.70 );

3. Create the target table with one column per month

Finally create the table to receive the data after the pivot which contains each month in a dedicated column.

CREATE TABLE [dbo].[Sales_Monthly_In_Columns]
(
  [YearID]     nvarchar(20) NOT NULL,
  [01_January]    numeric(10,2)  NULL,
  [02_February]   numeric(10,2)  NULL,
  [03_March]      numeric(10,2)  NULL,
  [04_April]      numeric(10,2)  NULL,
  [05_May]        numeric(10,2)  NULL,
  [06_June]       numeric(10,2)  NULL,
  [07_July]       numeric(10,2)  NULL,
  [08_August]     numeric(10,2)  NULL,
  [09_September]  numeric(10,2)  NULL,
  [10_October]    numeric(10,2)  NULL,
  [11_November]   numeric(10,2)  NULL,
  [12_December]   numeric(10,2)  NULL
  CONSTRAINT [SalesMonthlyColumnsPK] 
    PRIMARY KEY CLUSTERED (
      [YearID] ASC
    )
);
GO

4. Add components to the SSIS data flow

This part of the tutorial is the heart of the matter, as it is about setting up the data flow. First of all, you need to create a package within an SSIS project and configure a connection to a SQL Server database. Add these 3 components to an existing data stream, or create a new one.

OLE DB Source component
SSIS Pivot transformation
OLE DB Destination component

*Add the OLE DB components and the SSIS Pivot*

5. Configurer la source du flux de données SSIS de Pivot

Select the Sales_Monthly_In_Lines table as the source of the OLE DB Source component.

*Choose the source table created in the first step*

6. Configure the SSIS Pivot to rotate rows into columns

Now configure the SSIS Pivot with the following steps:

Choose the pivot key whose values become the name of the new columns: MonthName
Choose the key column that will remain in the output: YearID
Select the source column that contains the values: SalesAmount
Enter the list of columns created by the Pivot, separated by a comma: 01_January to 12_December
Generate the columns to fill the right area with the new pivoted columns.

The list of column names is as follows, without spaces because the SSIS Pivot component uses spaces in the automatically generated column names.

01_January,02_February,03_March,04_April,05_May,06_June,07_July,08_August,09_September,10_October,11_November,12_December

*Configure the SSIS Pivot options from the editor window to transform rows into columns*

It is only possible to fill in one Key column to pivot columns. To pivot a table with more grouping columns, use the advanced editor and SSIS column identifiers, this will be the subject of another tutorial dedicated to Pivoting with multiple fixed columns.

Set up the destination table with months in columns

Select the destination table to store the data after the Pivot in the SQL Server database. This is the dbo.Sales_Monthly_In_Columns table created in step 1.3 of the tutorial.

*Choose the target SQL Server table to store the data after the SSIS pivot*

From the Mappings tab, link each source column with the corresponding target column, i.e. the 12 columns generated for each month of data.

*Link pivoted source columns with columns in the target SQL Server table*

Once all components are configured, Visual Studio does not display any errors or warnings anymore. If it does, check the links and the data types to make sure they are compatible.

*SSIS Pivot to change rows into columns*

8. Run the SSIS package

Now run the package to transfer and rotate the SQL Server data from row format to column format. The 24 rows of the dataset, i.e. 24 months, are rotated into only 2 rows, i.e. 2 years.

*Successful execution of the SSIS Pivot in the dataflow*

9. Check data after Pivot using SSMS

Once the SSIS package is successfully executed, connect to the database with SQL Server Management Studio and execute these 2 data selection queries.

The result is consistent with the first table having 24 lines of data, i.e. 12 lines for 2021 and 12 lines for 2022 in the same column. As you you can see in the second query, the same amounts for 2021 on one row and the year 2022 on another row.

select * from [dbo].[Sales_Monthly_In_Lines];

select * from [dbo].[Sales_Monthly_In_Columns];

Conclusion on using SSIS Pivot with a simple table

To conclude this article on how to pivot rows into columns with SSIS Pivot, the native component allows you to easily pivot rows in a SQL table, but the table must have a simple structure. That is, it handles a single, static grouping column. For more complex cases, it is recommended to use the more flexible and easier to maintain T-SQL PIVOT query.