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.
Auf dieser Seite können Sie das Online-Video SQL Tutorial - PIVOT mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer BeardedDev 02 April 2018 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 145,878 Mal angesehen und es wurde von 1.8 tausend den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
