CODEWITHSUNDEEP
sqlsql-servert-sql
Mike Sarnoski
Mike Sarnoski

Reputation: 13

Reformat SQL Server Table Column Names and Pivot

I have a table stored in a SQL Server database that contains user ID, Product Name and values by week stored in columns. The column names for the weeks are named like this "trx_2015#08#07" and the value for that week is stored in each column:

UserId, ProductName, trx_2015#08#07, trx_2015#08#014, trx_2015#08#21

I would like to clean up those column names (I can't alter the table) to just be the week value (2015-08-07) then pivot the data to be in this format:

userID, ProductName, Week, Value

Any thoughts on the easiest way to accomplish this?

Upvotes: 1

Views: 202

Answers (1)

Joey
Joey

Reputation: 670

SQL Server provides a method for manipulating the data as you would like to with the use of the UNPIVOT relational operator. Below is an example of how to accomplish this in addition to dynamically obtaining the column names (ideally, we would not want to update the query every time a column is added).

Schema/Data Setup for Answer

CREATE TABLE tbl (
  UserId INT,
  ProductName NVARCHAR(1000),
  [trx_2015#08#07] INT,
  [trx_2015#08#014] INT,
  [trx_2015#08#21] INT
);

INSERT INTO tbl(UserId, ProductName, [trx_2015#08#07], [trx_2015#08#014], [trx_2015#08#21])
VALUES (1, 'Product #1', 123, 456, 789);

Dynamically obtaining trx column names

We will need an aggregate concatenation of the trx colmn names to pass to the UNPIVOT relational overator's IN clause.

SELECT STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
    TABLE_NAME = 'tbl'
    AND COLUMN_NAME LIKE 'trx_%'
  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')

Building the query

Ultimately, we would like the query to look like this:

SELECT UserId, ProductName, Value, trx 
FROM tbl 
  UNPIVOT(
    Value FOR trx IN ([trx_2015#08#014],[trx_2015#08#07],[trx_2015#08#21])
  ) AS unpvt

The query must be built dynamically in SQL because the IN clause here cannot accept a result set.

Query:

DECLARE @SQLString NVARCHAR(MAX);
SET @SQLString = CONCAT(
  'SELECT UserId, ProductName, Value, trx FROM tbl UNPIVOT(Value FOR trx IN (', 
  (SELECT STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
    TABLE_NAME = 'tbl'
    AND COLUMN_NAME LIKE 'trx_%'
  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')), 
  ')) AS unpvt'
);

EXEC(@SQLString);

Result Set:

UserId  ProductName Value   trx
1       Product #1  456     trx_2015#08#014
1       Product #1  123     trx_2015#08#07
1       Product #1  789     trx_2015#08#21

Upvotes: 1

Related Questions