Wednesday, December 9, 2015

Dynamically UnPivot a Pivoted table with example, SQL Server

First of all download the SQL Script, UnPivotTable.sql. Run it on your database, i.e. TEST_DB_UNPIVOT. A stored procedure, [dbo].[UnPivotTable], and a supporting function, [dbo].[SplitString],will be available on the database under DBO schema.

Now everything is you have to call [dbo].[UnPivotTable] with enough number of parameter. This procedure has eight (8) parameters. See the below description of all parameters. Luckily, every time you don’t have to use all parameters!

1
@dbName VARCHAR(100) = NULL
Put database name, where pivot table is available and unpivot table will be created. skip it if this procedure in the same database
2*
@tableNameWithSchema VARCHAR(100)
Put full name of input/Pivot table. i.e. [dbo].[TEST_PIVOT]
3
@unPivotTableNameWithSchema VARCHAR(100) = NULL
Put full name of output/unpivot table. i.e. [dbo].[TEST_PIVOT_UNPIVOT]. You can skip it to done with default script provided name.
4*
@measureName VARCHAR(100)
Put measure(s) name. Separated by comma (,). i.e. [UNITS],[SALES]
5*
@measureSearchString VARCHAR(100)
Put measure(s) search string from column name (separated by comma (,)-sync with Measure name), by which a particular measure of different period can be found. i.e. UNIT_US,SALES_US
6*
@dimColumnName VARCHAR(100)
Put column name on which you want to do unpivoe. i.e. [PERIOD]
7
@dimColumnStringStartPosFromMeasureColumnName VARCHAR(100) = NULL
Put start position of Dim Column value from measure column name of different period, this also sync with measure names. Luckily you can skip it
8
@dimColumnStringEndPosFromMeasureColumnName VARCHAR(100) = NULL
Put end position of Dim Column value from measure column name of different period, this also sync with measure names. Luckily you can skip it
 * Star refers that its mandatory

Let’s see below examples, how to call [dbo].[UnPivotTable]

1.    Let your database name is TEST_DB_UNPIVOT
2.    Run this script UnPivotTable.sql  on this database
3.    Let you have a pivot table name is [dbo].[TEST_PIVOT] and has below data
  
CTY_CODE
UNIT_US_201501
UNIT_US_201502
SALES_US_201501
SALES_US_201502
US
10
11
12.25
30
NK
56
60
43.56
50.96

4.    Now call [dbo].[UnPivotTable] … [see the message tab to know output table, as confirmation if succeed]

a.      EXEC [dbo].[UnPivotTable]
  @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'
            , @measureName = N'[UNITS],[SALES]'
            , @measureSearchString = N'UNIT_US,SALES_US'
            , @dimColumnName = N'[PERIOD]'

b.    EXEC [dbo].[UnPivotTable]
  @dbName = N'[TEST_DB_UNPIVOT]'
, @unPivotTableNameWithSchema = '[dbo].[TEST_PIVOT_UNPIVOT]'
            , @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'
            , @measureName = N'[UNITS],[SALES]'
            , @measureSearchString = N'UNIT_US,SALES_US'
            , @dimColumnName = N'[PERIOD]'

c.    EXEC [dbo].[UnPivotTable]
  @dbName = N'[TEST_DB_UNPIVOT]'
, @unPivotTableNameWithSchema = '[dbo].[TEST_PIVOT_UNPIVOT]'
            , @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'
            , @measureName = N'[UNITS],[SALES]'
            , @measureSearchString = N'UNIT_US,SALES_US'
            , @dimColumnName = N'[PERIOD]'
            , @dimColumnStringStartPosFromMeasureColumnName = N'9,10'
            , @dimColumnStringEndPosFromMeasureColumnName = N'15,16'

5.    Now execute “SELECT * FROM [dbo].[TEST_PIVOT_UPvt]” for (a) and “SELECT * FROM [dbo].[TEST_PIVOT_UNPIVOT]” for (b & c)
6.    For the above executions you will get the below output as unpivot table.

CTY_CODE
PERIOD
UNITS
SALES
US
201501
10
12.25
US
201502
11
30
NK
201501
56
43.56
NK
201502
60
50.96

see also -- UnPivot Pivoted Data using tSql on SQL Setver

No comments:

Post a Comment