SQL Tutorial - PIVOT
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- 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
www.amazon.com/gp/product/073...
T-SQL Fundamentals
www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
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. - วิทยาศาสตร์และเทคโนโลยี
Thanks a lot for explaining this so clearly! This is really helpful!!
The way you explain is incredible. Thank You :)
Thank you for this video. I've watched many others about pivot and yours is the first one that finally made it make sense for me.
That’s great, glad I could help
Thanks for including the syntax in the description for this and your unpivot video. I copied and annotated both of them for myself for reference. Hopefully that will help my comprehension and retention of these statements and make me more comfortable with them.
holicow your explanation about pivot is the best. I've been struggling with pivot for years
Ditto all that. Truly the best format so a person can understand the terms as well as what is happening. As he builds the pivot table when he writes over the you can understand what is happening. He uses terms like the spreader. Describing what is going to spread out or fan out the columns of data that will be displayed. Good job .....really good job.
Thanks so much.
Understandable, clear, and concise. Thank you!
Possibly the best explanation on the internet. Thank you.
Thanks so much.
Thank you so much for saying straight out how to use this. Referring to docs can be annoying when they're overly verbose.
another excellent BD video production!
Awesome, short and to the point. Was looking for something like this, not for the information genre you are using but something else. Works for me. Thumbs up and thanks!
Thanks for the feedback, what was it that you were looking for?
@@BeardedDevData I work with a technology platform that has multiple remote service agents on endpoints throughout the enterprise environment(the agents perform numerous functions on the endpoints depending on which ones are installed), there could be X number installed on one endpoint and a completely different number installed on another endpoint... and there could be/are numerous different versions of the agents installed on the endpoints. I just needed an example and apply the the 'months' as 'agent names' and 'sales revenue' as 'agent versions'. Cleared it up for me very easily! It is a more simpler solution than what I was working with in the past(CASE statements and lots of manipulation - never wrote a pivot in a sql query only Excel). Thanks again.
Thank you. It was very concise and comprehensive
Much apreciated for this tutorial. It was very helpful.
Nicely explained. Clear and concise with simple examples.
Thanks for the positive feedback, I’ve just posted two more videos on PIVOT if you want to check them out.
Thank you so much! Very clear explanation!
Thank you so much. I really appreciate your job.
this is the first video that worked for me, thank you!!
That's great to hear.
Absolutelly love your accent! Thank's for video
Thanks so much.
that was a really great explanation
0:25 "And it makes it easy to analoise data"
Thank you, Professor Higgins. 🙂
Great collection of videos. Thank you. It will be really helpfull if you could share tables or database used in the videos.
Excellent video thanks
you are the best !!
Thank you for posting the syntax
You got best videos in sql!
Thanks so much
Very good explained
that was very helpfull thankyou broo
Great stuff!
Thanks so much.
Awesomegreat teacher
nicely explained ..thanks
Nice one
Thanks! Very clear.
Perfect explanation
Thanks so much.
There should be a Heart react button for this video.
Thank you. ❤️
Thanks so much.
great video. thank you.
I LOVE YOU MAN !!!
Thanks great , in your second example where you use Sales as pivot columns I like to use as all 365 days (Calender Year) for example ('01-Jan-2019') , 02-Jan-2019 ….31-Dec-2019any guidance's please as I don't want to declare all the columns names with valuesThanksAny example for Dynamic value where the column values comes from data rather than type each column name?Yes do we have any example for for Dynamic pivot table?
How do you have a grand total at the end of the pivot table rows?
Thank you!
Very well explained!
Thanks so much.
Thank you BD
Thank you so much, bro
thanks teacher
Thanks so much. This is very helpfull.
thank you very much
hi brother! would you like to make video about dynamic pivot. thank you
Nice work there..
Thanks for the positive feedback.
what an info thanks
Thanks!
Thanks alot!
Thank you sir
any thought on doing a video on dynamic multi aggregate pivot tables? Whereby you can declare and pass parameters that define which values you want as the row definition and also specify the different aggregates and columns. IE Say you want a count of one field, a sum of a couple others and avg of a third but as the year progresses or topic changes you'd like to just change the input and not re hard code the whole multi aggregate pivot query.
It’s a good idea
Thank you
I have a table where I need to multiply within my aggregate function in the pivot but when I put the * withing the sum function to multiply the two together, I get an error. is there a different way to multiply within a pivot?
Pivot
(
sum(CM_COST * Q_SHIP)
FOR [Month]
in ([January]
, [February]
, [March]
) AS Pvt
This is what my pivot portion looks like
My problem with pivot is that the "Spreading Elements" need to be hardcoded into the query, thus new elements go missing.
You got the solution?
Great video and nice explanation. I was wondering if we could automatically generate the column names based on the distinct values in the original column. (Like how pivot works in pandas python).
That is one of the main limitations of the pivot syntax. You can build it up dynamically but it does make the query hard to read.
@@BeardedDevData Could you create a video on loops using SQL. Thanks in advance
What does the [] surrounding month and January,February,March do?
I was testing on sqliteonline.com and removed all the [] and pivot still worked, but when the new columns were integers 1,2,3... removing the [] around original column name is fine but [1],[2],[3] without brackets like IN (1,2,3..) will break with "Incorrect syntax near '1'."
It actually does nothing, it's just something I do as a habit when working with PIVOT, when creating columns if they start with regular identifiers such as letters a-z then [] are not required, integers are considered irregular therefore [] are required, you can leave them out if you wish
Great video. Very helpful. what if you dont have elements names, can it be dynamic?
Hi Maurice, I have got a separate couple of videos on dynamic pivoting: th-cam.com/video/SzYHiuSy0ZU/w-d-xo.html, th-cam.com/video/n9d8WSEBzAY/w-d-xo.html
How do you know hat the column one is equivalent to supplier ID 1 ? Is there no ordering required ?
The order of the columns is specified in select.
You should do a dynamic pivot table for customer Id’s 👍🏻 seeing as they can always change.
I was just about to ask if this was possible
Yes do we have any example for for Dynamic pivot table?
Here is one way to do it: th-cam.com/video/BZ8ojeOkRUg/w-d-xo.html
can u please tell how can we add a row/ column total to the pivot table
Hi Nikhil, I will be posting a video on this over this week, is there specific types of columns/rows you are looking to add such as totals?
How do you do the opposite of pivot? If your data is in a column header i.e the first row/header of data, how do you make those contents populate one column, row by row, going down?
Take a look at UNPIVOT: m.th-cam.com/video/0ERIr8UAK1s/w-d-xo.html
@@BeardedDevData thanks man!
Let say you need to add several cases within the pivot. Can you do that?
Can you give me more detail on what you mean by cases
Could you use max/min in this for varchar values? So far I've not been successful in doing that, Hench the reason I am here.
Yes, you have to use MIN or MAX when working with varchar. I’m actually planning a video on this next week but if you want to send me some more details I will take a look.
Hello. I'm watching this playlist because I've watched every single minute of the window functions, which I've loved so much. One question: do we have to hardcode the names of the new columns in the part as well as after the IN? I find it quite inefficient and clunky. No way to make it code dependent? I can imagine that we can use a SELECT statement after the IN, but what about the section? Thanks!
Hi, I have separate videos on how to create this dynamically, this is part 1: th-cam.com/video/SzYHiuSy0ZU/w-d-xo.html
This is part 2: th-cam.com/video/n9d8WSEBzAY/w-d-xo.html
@@BeardedDevData Thanks. I've watched it and it's pretty clear. I'll stick to part 2 for simplicity. So, there's no way to have multiple aggregations other than using grouping by SELECT CASE statement or joining multiple PIVOT operations, right? Just asking in case they made improvements after the release of your videos. P.S: I've really loved this playlist, so much so, I've watched every single minute of them. Thank you!
Not that I'm aware of in all honesty.
For the second example, the table displayed shows aggregated total sales_amount for each customer id over the months. Can you explain how the table is aggregated by the months?
Month is part of the original source query but is not used as part of the pivot in the second example. What we are doing is asking for Month in addition to Sales_Amount pivoted by Customer_Id. I think I need to put up some more examples regarding pivot.
@@BeardedDevData This was great but like you stated - yes, we need a few more examples of pivot where you can 'hammer home' the inner workings of it so folks like me can fully get it.
Hi I need help 😥I want to display the average in 2 decomal places but Idk how
SELECT Student, [English], [Mathematics], [Science], [Programming], [History]
FROM ( SELECT Student, Grades, Subject
FROM Grade_Report) AS SourceTable
PIVOT
(
AVG (Grades)
FOR
Subject IN ([English], [Mathematics], [Science], [Programming], [History])
) AS PivotTable
It's one of the downsides of PIVOT when working with decimals, you can't do the conversion within the PIVOT statement therefore you need to do this in the outer select, SELECT, Student, CAST([Mathematics] AS DECIMAL(6, 2)) AS [Mathematics] ...
@@BeardedDevData Thank you very much! I was able to figure it out after I posted this. I have the same idea. 👊🏽🍾
Is there a reverse pivot? I.e. convert columns to rows?
You can check out my video on UNPIVOT - th-cam.com/video/0ERIr8UAK1s/w-d-xo.html
@@BeardedDevData Thanks
Any idea how to do this using Hive?
I believe in Hive to PIVOT data you have to use the map function.
If I have another table with customer name and customer ID and I want to Inner JOIN and display the customer name instead of the customer ID how do I go about it?
SELECT
[CustomerName] -- (
That's very odd, your syntax looks correct, I have even tried it and it works, what RDBMS are you using?
can u show me,
how to sum by column =>> total of sum(1),sum(2),sum(3),....
Hi, you might find this video useful if you want to add totals, th-cam.com/video/YPZwnb1WX9I/w-d-xo.html.
What I can understand is where are you getting [Month] from?
Hi Darryl, I use DATENAME(MONTH, sales_date) to retrieve the month name, in the pivot query I just type them in.
munth
Thanks BeardedDev for your videos, they are very useful and informative. Is there any way to get the database you use for your examples? Is it freely available?
Hi, thanks for the positive feedback, it is not something that is available yet but something I'm looking to do in the future
@@BeardedDevData Please is it yet ready to share the Sales Database with us ... it will be very helpful to apply your examples after watching your video
What is wrong with my pivot?
SELECT [United States],
[Asia],
[Canada]
FROM
(
SELECT
(SELECT country FROM regions r WHERE r.region_id = e.region_id) as [country]
FROM employees e
) AS Src
PIVOT
(
COUNT(country)
FOR country IN ([Asia],[Canada],[United States])
) AS Pivot
Changing the table alias from Pivot to something like Pvt should solve the error, it's because Pivot is a recognised keyword.
@@BeardedDevData Thanks for the suggestion. I changed it to Pvt , but am now getting this error :
ERROR: syntax error at or near "["
LINE 1: SELECT [United States],
What database system are you using?
@@BeardedDevData Postgres
I'm not that familiar with postgres, I had a look and there is a crosstab function that looks similar to pivot, as an alternative you can take a look at this video, th-cam.com/video/xPMbgM8Eb2k/w-d-xo.html
how to summarize monthly data in annual level
First you will need to create your derived table with a year column, month column and then amount column, remember you can sum in the derived table if your data is at day level for example. Once that's done then you can perform the pivot operation.
@@BeardedDevData Thanks Sir
now i wanna to save that creating column I mean the summ of 12 month in current table ?
what shoul I do?
Where is the accent from? It sounds so cool.
I’m from England.
no much advertisments
video pivot table using mysql -->th-cam.com/video/N34zwKoxQOA/w-d-xo.html
Me: month
BeardedDev: munth
But in all seriousness great idea to replace the syntax elements by actual values one by one to hammer home the concept. Well done.
Thank you for acknowledging that I pronounce the word correctly. The word, “Month” in British is pronounced muhnth, I am British, hence the pronunciation 👍.
say month good
i'am barely deaf!!
Thanks!