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/0735685045/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735685045&linkCode=as2&tag=beardeddev05-20&linkId=e93b0f7483b1fa06aca5a3aec4f7764b
T-SQL Fundamentals
https://www.amazon.com/gp/product/150930200X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=150930200X&linkCode=as2&tag=beardeddev05-20&linkId=70aa2ffa3e84cab66cb964ca1e9cee62
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/0735658366/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735658366&linkCode=as2&tag=beardeddev05-20&linkId=b88746aea666c9f98da147e70b4491ca
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.
Видео SQL Tutorial - PIVOT канала BeardedDev
In this video I talk about using PIVOT in Microsoft SQL Server.
T-SQL Querying
https://www.amazon.com/gp/product/0735685045/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735685045&linkCode=as2&tag=beardeddev05-20&linkId=e93b0f7483b1fa06aca5a3aec4f7764b
T-SQL Fundamentals
https://www.amazon.com/gp/product/150930200X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=150930200X&linkCode=as2&tag=beardeddev05-20&linkId=70aa2ffa3e84cab66cb964ca1e9cee62
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/0735658366/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735658366&linkCode=as2&tag=beardeddev05-20&linkId=b88746aea666c9f98da147e70b4491ca
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.
Видео SQL Tutorial - PIVOT канала BeardedDev
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
SQL TUTORIAL - GROUP BY, HAVING, Aggregate FunctionsSQL Interview Questions - What is the difference between Rank and Dense RankHow to create an Azure SQL DatabaseT-SQL Tutorial - When is DISTINCT evaluatedSQL Development - Cross JoinsSQL Tutorial - Difference between CTEs and Derived TablesSQL Tutorial - Subqueries (Scalar Valued)SQL for Beginners Part 4 - DISTINCTQlikView Tutorial - Load InlineSQL Interview Questions - FizzBuzz ChallengeSQL TUTORIAL - Add ColumnT-SQL Tutorial - REPLICATESQL Tutorial - How to create a Dynamic Pivot in SQL Part 2SQL Sunday Quiz - Week 1SQL TUTORIAL - CTEs Part 1SQL Sunday Quiz - Week 4SQL Tutorial - Window Functions (Follow Along)How I became an Microsoft Certified Solutions Expert in Data Management and AnalyticsSQL Tutorial - How to drop a column from a tableDifferent Roles Working With DataSQL Tutorial - Views