Multi Columns Value Pivot in SSIS

Introduction

In this article I am trying to demonstrate a simple example with a single value columns pivoting by SSIS and then try to add more columns on it.

Hope it will be interesting.

Case Scenario

I have a table

IDSALESPERSON	SALESPERSON	SALESYEAR	NORTRESIONVAL	SOUTHRESIONVAL
101	Joydeep Das	2015	5.00	7.00
101	Joydeep Das	2016	10.00	12.00
102	Deblina Bhatta	2015	2.00	12.00
102	Deblina Bhatta	2016	15.00	17.00

Step-1

First we made a simple Pivot and get the result like

SALESPERSON	2015_NORTRESIONVAL	2016_NORTRESIONVAL
Joydeep Das	5.00	10.00
Deblina Bhatta	2.00	15.00

It contains Single Value columns Pivot Using columns name NORTRESIONVAL

Step-2

Now I want to Add the columns named IDSALESPERSON in this Pivot Result.

SALESPERSON	IDSALESPERSON	2015_NORTRESIONVAL	2016_NORTRESIONVAL
Joydeep Das	101	5.00	10.00
Deblina Bhatta	102	2.00	15.00

Step -3

Now I want to add SOUTHRESIONVAL

SALESPERSON	IDSALESPERSON	2015_NORTRESIONVAL	2016_NORTRESIONVAL
Joydeep Das	101	5.00	10.00
Deblina Bhatta	102	2.00	15.00

2015_ SOUTHRESIONVAL	2016_ SOUTHRESIONVAL
7.00	12.00
12.00	17.00

Hope you will understand the scenario.

Data Flow Task

Step – 1 [ Configuring SSIS PIVOT Transform ]

Your actual work is done. Now we have to Change the column name from

 C_2015_ SOUTHRESIONVAL à to à 2015_ SOUTHRESIONVAL

Right click the Pivot Transform and click the Show Advance Editor

Output

Step – 2  [ Add a new columns IDSALESPERSON in Pivot Result ]

·         0 – the column is passed through unaffected

·         1 – the column values become the rows of the pivot (aka the Set Key)

·         2 – the column values become the column names of the pivot (aka the Pivot Column)

·         3 – the column values that are pivoted in the pivot

Here [Source Column] = [LineageID]  of the Specified columns

Output:

Step – 3 [ Now Add other Columns as a Part of Pivot ]

Hope you like it.

Posted by: MR. JOYDEEP DAS