SQL Tutorial - UNPIVOT смотреть онлайн

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

If you like the video you can support me on Patreon, https://www.patreon.com/beardeddev

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;