Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Use SQL Server's UNPIVOT operator to dynamically normalize output


By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | More > T-SQL

Problem

In my previous tip, Use SQL Server's UNPIVOT operator to help normalize output, I discussed how to use UNPIVOT to present columns as rows. The technique works well when you have a finite and known number of columns, but might not work so well if your schema is evolving or your non-normalized table is very wide. In cases like this, you may need to construct your UNPIVOT query dynamically.  Check out how to do so in this tip.

Solution

Taking the previous sample data:

CREATE TABLE dbo.CustomerPhones
(
  CustomerID INT PRIMARY KEY, -- FK
  Phone1 VARCHAR(32),
  Phone2 VARCHAR(32),
  Phone3 VARCHAR(32)
);
INSERT dbo.CustomerPhones
  (CustomerID, Phone1, Phone2, Phone3)
VALUES
  (1,'705-491-1111', '705-491-1110', NULL),
  (2,'613-492-2222', NULL, NULL),
  (3,'416-493-3333', '416-493-3330', '416-493-3339');

We know that the query we want to end up with looks like this:

SELECT CustomerID, Phone
FROM
(
  SELECT CustomerID, Phone1, Phone2, Phone3 
  FROM dbo.CustomerPhones
) AS cp
UNPIVOT 
(
  Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS up;

But what if we have 800 columns named PhoneN? What if we add a few new PhoneN columns every few days? What if we actually have 250 unrelated columns that don't have a Phone prefix, but we still want to transpose into rows? Maintaining a query with a constantly moving underlying schema, or even just generating the text for a query that covers 800 columns, is not going to be fun (and again, we'll ignore the fact that the schema was not designed for fun in the first place).

In order to generate such a query dynamically, we can look to sys.columns to derive the column names. Let's say we know the table name and the key column(s), and a pattern the column name should match, then we can pull all of the other columns and build a comma-separated list. In this case, it would look like this:

-- incoming parameters
DECLARE 
  @table        NVARCHAR(257) = N'dbo.CustomerPhones', 
  @key_column   SYSNAME       = N'CustomerID',
  @name_pattern SYSNAME       = N'Phone[0-9]%';
-- local variables
DECLARE 
  @sql  NVARCHAR(MAX) = N'',
  @cols NVARCHAR(MAX) = N'';
SELECT @cols += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID(@table)
  AND name <> @key_column
  AND name LIKE @name_pattern;
SELECT @sql = N'SELECT ' + @key_column + ', Phone
  FROM 
  (
    SELECT ' + @key_column + @cols + '
 FROM ' + @table + '
  ) AS cp
  UNPIVOT
  (
    Phone FOR Phones IN (' + STUFF(@cols, 1, 1, '') + ')
  ) AS up;';
PRINT @sql;
-- EXEC sp_executesql @sql;

The output:

SELECT CustomerID, Phone
  FROM 
  (
    SELECT CustomerID, [Phone1], [Phone2], [Phone3]
 FROM dbo.CustomerPhones
  ) AS cp
  UNPIVOT
  (
    Phone FOR Phones IN ( [Phone1], [Phone2], [Phone3])
  ) AS up;

And of course, when we execute this instead of print it, we get the exact same results as in the previous tip:

Results of UNPIVOT query

We can add 20 or 500 more columns named Phone4 -> PhoneWhatever, and we wouldn't need to change this code (unless we got to a point where the query text was too large).

Another case - SQL Server UNPIVOT with Two Related Columns

Adapting this code to the case where we had two related columns that needed to be presented together requires very little modification. Assuming this sample data:

CREATE TABLE dbo.CustomerPhones2
(
  CustomerID INT PRIMARY KEY, -- FK
  Phone1 VARCHAR(32),
  PhoneType1 CHAR(4),
  Phone2 VARCHAR(32),
  PhoneType2 CHAR(4),
  Phone3 VARCHAR(32),
  PhoneType3 CHAR(4)
);
INSERT dbo.CustomerPhones2 VALUES
  (1,'705-491-1111', 'cell', '705-491-1110', 'home', NULL,NULL),
  (2,'613-492-2222', 'home', NULL, NULL, NULL, NULL),
  (3,'416-493-3333', 'work', '416-493-3330', 'cell',
     '416-493-3339', 'home');
GO

We just need a second variable to hold the related column names (and we make the assumption that you only ever add Phone4 and PhoneType4 together).

-- incoming parameters
DECLARE 
  @table         NVARCHAR(257) = N'dbo.CustomerPhones2', 
  @key_column    SYSNAME       = N'CustomerID',
  @name_pattern1 SYSNAME       = N'Phone[0-9]%',
  @name_pattern2 SYSNAME       = N'PhoneType[0-9]%';
-- local variables
DECLARE 
  @sql   NVARCHAR(MAX) = N'',
  @cols1 NVARCHAR(MAX) = N'',
  @cols2 NVARCHAR(MAX) = N'';
SELECT @cols1 += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID(@table)
  AND name <> @key_column
  AND name LIKE @name_pattern1;
SELECT @cols2 += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID(@table)
  AND name <> @key_column
  AND name LIKE @name_pattern2;

SELECT @sql = N'SELECT ' + @key_column + ', Phone, PhoneType
FROM 
(
  SELECT ' + @key_column + ', Phone, PhoneType,
    idp = SUBSTRING(Phones, LEN(Phones) - PATINDEX(''%[^0-9]%'', REVERSE(Phones)) + 2, 32),
    idpt = SUBSTRING(PhoneTypes, LEN(PhoneTypes) - PATINDEX(''%[^0-9]%'', REVERSE(PhoneTypes)) + 2, 32)
  FROM
  (
    SELECT ' + @key_column + @cols1 + @cols2 + '
    FROM ' + @table + '
  ) AS cp
  UNPIVOT 
  (
    Phone FOR Phones IN (' + STUFF(@cols1, 1, 1, '') + ')
  ) AS p
  UNPIVOT
  (
    PhoneType FOR PhoneTypes IN (' + STUFF(@cols2, 1, 1, '') + ')
  ) AS pt
) AS x
WHERE idp = idpt;';
PRINT @sql;
-- EXEC sp_executesql @sql;

Output:

SELECT CustomerID, Phone, PhoneType
FROM 
(
  SELECT CustomerID, Phone, PhoneType,
    idp = SUBSTRING(Phones, LEN(Phones) - PATINDEX('%[^0-9]%', REVERSE(Phones)) + 2, 32),
    idpt = SUBSTRING(PhoneTypes, LEN(PhoneTypes) - PATINDEX('%[^0-9]%', REVERSE(PhoneTypes)) + 2, 32)
  FROM
  (
    SELECT CustomerID, [Phone1], [Phone2], [Phone3], [PhoneType1], [PhoneType2], [PhoneType3]
    FROM dbo.CustomerPhones2
  ) AS cp
  UNPIVOT 
  (
    Phone FOR Phones IN ( [Phone1], [Phone2], [Phone3])
  ) AS p
  UNPIVOT
  (
    PhoneType FOR PhoneTypes IN ( [PhoneType1], [PhoneType2], [PhoneType3])
  ) AS pt
) AS x
WHERE idp = idpt;

And then when the dynamic SQL string is executed, the output is:

Results of UNPIVOT query

Yet another case - SQL Server UNPIVOT with Multiple Data Types

A complication is that all of the column values - as with UNION, INSERSECT and CASE - need to have compatible data types. So, you may need to actually perform some conversions in order to make all of the possible values compatible. Consider the following, equally questionable design and sample data:

DROP TABLE dbo.CustomerEAV;
CREATE TABLE dbo.CustomerEAV
(
  CustomerID INT PRIMARY KEY,
  ShoeSize   DECIMAL(3,1),
  Waist      INT,
  Monogram   VARCHAR(20),
  Email      VARCHAR(320)
);
INSERT dbo.CustomerEAV
  (CustomerID,ShoeSize,Waist,Monogram,Email)
VALUES
  (1,12,36,'AMB','[email protected]'),
  (2,11,34,'BRC','[email protected]'),
  (3, 9,28,'JRJ','[email protected]');

And again imagine that we need to write our query such that it automatically accounts for any columns we might add to the table in the future. The ideal query would look something like this:

SELECT CustomerID, Property, Value
FROM
(
  SELECT CustomerID, 
    [Email] = CONVERT(VARCHAR(320), [Email]), 
    [Monogram] = CONVERT(VARCHAR(320), [Monogram]), 
    [ShoeSize] = CONVERT(VARCHAR(320), [ShoeSize]), 
    [Waist] = CONVERT(VARCHAR(320), [Waist])
   FROM dbo.CustomerEAV
) AS t
UNPIVOT
(
  Value FOR Property IN ( 
    [Email], 
    [Monogram], 
    [ShoeSize], 
    [Waist])
) AS up;

And to generate that dynamically:

DECLARE 
  @table         NVARCHAR(257) = N'dbo.CustomerEAV', 
  @key_column    SYSNAME       = N'CustomerID';
DECLARE 
  @colNames  NVARCHAR(MAX) = N'',
  @colValues NVARCHAR(MAX) = N'',
  @sql       NVARCHAR(MAX) = N'';
SELECT 
  @colNames += ', 
    ' + QUOTENAME(name), 
  @colValues += ', 
    ' + QUOTENAME(name) 
   + ' = CONVERT(VARCHAR(320), ' + QUOTENAME(name) + ')'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
AND name <> @key_column;
SET @sql = N'SELECT CustomerID, Property, Value
FROM
(
  SELECT ' + @key_column + @colValues + '
   FROM ' + @table + '
) AS t
UNPIVOT
(
  Value FOR Property IN (' + STUFF(@colNames, 1, 1, '') + ')
) AS up;';
PRINT @sql;
-- EXEC sp_executesql @sql;

The output from the PRINT command is as above; and when we uncomment the EXEC, we get the following output:

Results of dynamic UNPIVOT query

Note that I got some inspiration on this technique from StackExchange user bluefeet, who is the unofficial PIVOT and UNPIVOT queen, and gave this excellent answer in 2012.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, May 31, 2016 - 4:36:42 PM - Nancy Back To Top

 I am also trying to add one more column to the unpivoted results.  Columns will be dynamic based on the table name, and I am trying to return current value and previous value for each column from an audit table.

 


Wednesday, November 04, 2015 - 2:49:23 PM - Kevin Back To Top

This is a basic question but I am very new to sql.  so after the dynamic unpivot structures the data the way it is needed, how do you save that as a new table?  I don't want to run this process every time I need to view the data.

Thanks,

Kevin


Friday, March 06, 2015 - 11:17:11 AM - samy Back To Top

thanks ,really super example

but i got the below error messg

 

The type of column "column_name" conflicts with the type of other columns specified in the UNPIVOT list.

 

 


Wednesday, December 10, 2014 - 2:40:01 PM - PK Back To Top

It was a super example. Thanks


Monday, November 03, 2014 - 1:11:20 AM - Thiru Back To Top

How to Transfer Collumn into Row in SQl Select Query result Table

EX:

My select Query result is 

insrtno Bankglcode  reason

51000   17589         Misplaced by user

 

i need of result is 

instrno         51000

Bankglcode    17589

reason          Misplaced by user

 

any sample Query  send me

 

 


Wednesday, October 01, 2014 - 2:36:33 PM - Doug Back To Top

Hi Aaron,

Nice, well-written article. Thanks for your time and effort.

I was thinking I'd like to add some functionality to this code. We're doing a LOT of ETL right now and I was thinking I'd like to squirt a string of comma delimited field names into a function, parse them into individual column names in place of your call to sys.columns OR as a parameter to the sys.columns call. Any suggestions on how best to do this (as in show where the alligators are *g*) ?

I'd like to eventually 'drive' this via XML-based content in order to accommodate different data sources whose structure I have no control over at the point of entry in to our system.

Thanks again!

 


Thursday, March 06, 2014 - 5:53:56 PM - Brenda Back To Top

Thanks!  This is exactly what I need to normalize data from numerous imports of wide flat files.  You've saved me hours figuring out how to do this dynamically!


Friday, February 14, 2014 - 9:05:27 PM - suresh Back To Top

 thanks . Good one


Learn more about SQL Server tools