SQL Tutorial - PIVOT
Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
In this video I talk about using PIVOT in Microsoft SQL Server.
T-SQL Querying
https://www.amazon.com/gp/product/073...
T-SQL Fundamentals
https://www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/073...
Using PIVOT allows us rotate data from rows to columns making the data more readable. PIVOT is ideal for reporting in SQL Server and can be used as a starting point to visualise trends.
In this PIVOT tutorial I demonstrate the difference between how data is presented compared to GROUP BY.
PIVOT Syntax
SELECT [columns] FROM
(
[source_query]
)
PIVOT
(
[aggr_function]([aggr_column]
FOR [spreading_column] IN ([spreading_elements]))
SQL Queries in the video:
Results using GROUP BY statement
SELECT
Sales_Customer_Id
, DATENAME(MONTH, Sales_Date) AS [Month]
, Sales_Amount
FROM dbo.Sales
GROUP BY
Sales_Customer_Id
, DATENAME(MONTH, Sales_Date)
Results using PIVOT statement
SELECT
Sales_Customer_Id
, [January]
, [February]
, [March]
FROM
(
SELECT
Sales_Customer_Id
, DATENAME(MONTH, Sales_Date) AS [Month]
, Sales_Amount
FROM dbo.Sales
) AS Src
PIVOT
(
SUM(Sales_Amount)
FOR [Month] IN ([January], [February], [March])
) AS Pvt
Alternative PIVOT statement
SELECT
[Month]
, [1]
, [2]
, [3]
, [4]
, [5]
, [6]
, [7]
, [8]
FROM
(
SELECT
Sales_Customer_Id
, DATENAME(MONTH, Sales_Date) AS [Month]
, Sales_Amount
FROM dbo.Sales
) AS Src
PIVOT
(
SUM(Sales_Amount) FOR Sales_Customer_Id IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS Pvt
Please feel free to post comments.
On this page of the site you can watch the video online SQL Tutorial - PIVOT with a duration of hours minute second in good quality, which was uploaded by the user BeardedDev 02 April 2018, share the link with friends and acquaintances, this video has already been watched 145,878 times on youtube and it was liked by 1.8 thousand viewers. Enjoy your viewing!
