Sunday 3 July 2016

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