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