SQL Tutorial - UNPIVOT

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

In this SQL tutorial we take a look how to perform UNPIVOT, UNPIVOTING data means transposing data from columns in to rows. I talk through the syntax of UNPIVOT in Microsoft SQL Server and how we will actually be creating two new columns; one will hold the existing column names and another to hold the values.

In this tutorial we take a look at a typical example of PIVOTed data that we might be working with, in this case survey responses where each column is a question and the rows store the responses against the surveyid.

I also discuss a common problem with UNPIVOT and that is forgetting to alias the expression and explain how logical query processing works, it looks like we are selecting columns that don't exist in a table but as UNPIVOT is evaluated in the FROM clause of a query that is in turn evaluated before the SELECT clause the existing columns are no longer available to us, only the UNPIVOTed columns.

If you would like to follow along with this SQL tutorial and improve your knowledge of UNPIVOT the code examples are below.

Please feel free to post any comments.

DROP TABLE IF EXISTS dbo.SurveyResponses;

GO

CREATE TABLE dbo.SurveyResponses
(
SurveyId INT NOT NULL IDENTITY(1, 1),
[How clean was your room] TINYINT NOT NULL,
[How satisfied are you with the facilities] TINYINT NOT NULL,
[How was breakfast] TINYINT NOT NULL,
[Will you stay here again] TINYINT NOT NULL
);

INSERT INTO dbo.SurveyResponses ([How clean was your room], [How satisfied are you with the facilities], [How was breakfast], [Will you stay here again])
VALUES
(4, 4, 3, 1),
(2, 3, 4, 1),
(1, 1, 4, 0),
(4, 5, 5, 1),
(2, 3, 5, 1);

-- UNPIVOT SYNTAX
SELECT
[columnlist],
[namescolumn],
[valuescolumn]
FROM [table]
UNPIVOT
(
[valuescolumn] FOR [namescolumn] IN ([sourcecolumns])
) AS U;

SELECT
surveyid,
Question,
Answer
FROM dbo.SurveyResponses
UNPIVOT
(
[Answer] FOR [Question] IN
(
[How clean was your room],
[How satisfied are you with the facilities],
[How was breakfast],
[Will you stay here again]
)
) AS U;

[QuietLumina]

Still helping people 2 years later. I appreciate this so much!! Thank you!

[colinmacguire]

Very useful and clear. Helped me solve my challenge. Thank you!

[yueliang]

Clear and well explained! Thanks for the turoial!!

[allmazd]

So, there is another technique to obtaine same unpivot operation by using cross apply + value. Can you make a video that describes this approach in details, thank you in advance.

[ck]

How to deal with NULL values in unpivot? I have some null values in value column? thx.

[Han-veuh]

2. Is it something to do with the aggregation after pivot is an irreversible operation and data --> PIVOT (with aggregate used) --> UNPIVOT --> does not recover data?
Practically why would people want to unpivot if it does not do aggregation and thus does not generate new information? (any reasons beyond just presentation?)
3. How can we show the null cells (in the pivot table) after UNPIVOT?
4. Is there a link to the video about why all TINYINT when unpivot?