SSIS: Unpivot Transformation

Unpivot is the opposite to Pivot transformation. Using Unpivot transformation we can interchange the columns into rows.

Example:
Source table:

CREATE TABLE UNPIVOT_SOURCE

(

YEARS INT,

SOAP NUMERIC(12,4),

BRUSH NUMERIC(12,4),

PASTE NUMERIC(12,4)

)

INSERT INTO UNPIVOT_SOURCE VALUES

(2010,3500.25,3201.45,2100.61)

,(2011,2842.15,3654.75,2236.24)

,(2012,2689.26,3214.23,2432.55)

Target table:

CREATE TABLE UNPIVOT_TARGET

(

YEARS INT,

PRODUCT NVARCHAR(30),

TOTAL_PRICE NUMERIC(12,4)

)

SSIS Package:

Connect to the SQL Server and create the above tables.
Open SSIS Package, In 'Control Flow' drag and drop 'Data flow task' from Toolbox.
Go to Data flow and drag and drop 'OLE DB Source' and configure with source table.
From Toolbox drag and drop 'Unpivot Transformation', and connect 'OLE DB Source' green arrow.

Edit the 'Unpiot transformation'. Select the columns check box, which we are going to convert as rows (Soap, Brush and Paste).

In the above screen we can see the 'Input Column', 'Destination Column' and 'Pivot key value'. In the 'Destination Column' section we need to give destination column name(TOTAL_PRICE). The source columns 'Soap, Brush and Paste are having total cost information, this information is related to 'TOTAL_PRICE' (destination column).
In the bottom of the above screen we can see the 'Pivot key value column name' text box. There we need to give the 'Name' for the new Unpivot column (PRODUCT).

O/P:

SSIS