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 DBOschema.
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
