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.
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
@@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],
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, ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xPMbgM8Eb2k.html
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.
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.
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] ...
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?
Hi Maurice, I have got a separate couple of videos on dynamic pivoting: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SzYHiuSy0ZU.html, ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-n9d8WSEBzAY.html
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).
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?
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!
@@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!
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
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?
@@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
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.
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.
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?
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.
Thank you for acknowledging that I pronounce the word correctly. The word, “Month” in British is pronounced muhnth, I am British, hence the pronunciation 👍.
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!
@@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.
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?
Appreciate that, that's why I have separate videos on dynamic pivoting. You will find though even writing a basic query you will need to know the data.
