This was so useful, damn I was just told that I would be using this for taking data from certain tables and so on so this is soo so handy, thank you so much
I think a better dataset would have lead to a clearer "pivot table", I understand where/why the NULL's are coming, however not everyone might understand how a different dataset would have been more interesting (such as demographics by city, county state). That said you did a great job explaining the how/why of the SQL.
I always got error with the statement below, I have no ideas why A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.
@@AnthonySmoak sir this is my code SET @query = ' SELECT * INTO #TempPivot FROM ( SELECT DepartmentCode, '+ @cols+ ' FROM ( SELECT DepartmentCode, Label, isnull(Amount, 0) AS Amount FROM #temp UNION ALL SELECT ''Total'', Label, SUM(Amount) FROM #temp GROUP BY Label ) AS ResultTable PIVOT ( SUM(Amount) FOR Label IN (' + @cols + ') ) AS PivotTable ) AS FinalResult; -- Select from the temporary table SELECT * FROM #TempPivot; -- Drop the temporary table DROP TABLE IF EXISTS #TempPivot;'; -- Execute the dynamic SQL query EXEC sp_executesql @query; but it gives this error Incorrect syntax near the keyword 'COALESCE'. pls can you help on this.
DECLARE @cols AS NVARCHAR(MAX); DECLARE @query AS NVARCHAR(MAX); -- Get the distinct label values dynamically SET @cols = STUFF( ( SELECT DISTINCT ', COALESCE(' + QUOTENAME(Label) + ', 0) AS ' + QUOTENAME(Label) FROM #temp FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '' ); sorry code this I missed
it is showing an error Msg 8156, Level 16, State 1, Line 6 The column 'Feb 1 2018 12:00AM' was specified multiple times for 'Q'. Msg 208, Level 16, State 0, Line 34 Invalid object name '##TBL_TEMP1'.
Hey Anthony, great job in explaining the pivot function. I have a question to ask, it would be great if you could reply - How can we handle a scenario where the 'city' column would have more than 10,000 unique cities (let's assume) ?
well I mean, if it needs to be readable I'd use another reference like country or so. Maybe try to orient the query to a specific type of data like above certain numer?
Hi Anthony, I have a quick questions, how would you modify this query for a different scenario if there are two fields that needs to be filled in the Pivot for a single city, so for example let us assume 6118 and 6181 both should have rents assigned to sanfrancisco, if you see in your scenario no one city has got more than one rent filled in its rows
Thank you! Here, I don't want to display 'NULL' or '0' in the result, so that we can view a clear picture of the output. Can you please provide the query for it?
hi there lovely video however i am having a problem, by your method i am able to get the columns dynamically but problem is they are not unique and repeating value, just like it has combine all the row data in one cell, can you help out on this
If you first Select Distinct on the column you are going to pivot and store it in a temp table IE #ColumnNames You can then use that in place of the (Cities) column in the pivot and you will get unique columns.
If your data is in Excel, in order for SQL to work, you would need to import it into a relational database first. Excel is not a relational database. However, you can use the unpivot functionality in Power Query to get the same results as the SQL in this video. anthonysmoak.com/2017/07/16/easily-unpivot-your-data-in-excel-using-power-query/
This was a tremendous help to get my similar query to work. Thanks much!
Glad to hear it! Thanks for the comment.
Great job explaining PIVOT function, you were easy to listen to and understand
I appreciate it, thank you!
You saved my days…. Thanks
Happy to help!
best pivot example on youtube
Thank you, I'm glad I was able to help!
This is very helpful. Thank you
Glad to hear it. Thanks!
Great contents and examples, Loved it
Awesome video. Helped me get a clear grasp on pivots.
I appreciate the comment!
This was so useful, damn I was just told that I would be using this for taking data from certain tables and so on so this is soo so handy, thank you so much
Fantastic! I enjoy hearing how my channel helps people with their data. Thanks for commenting!
I think a better dataset would have lead to a clearer "pivot table", I understand where/why the NULL's are coming, however not everyone might understand how a different dataset would have been more interesting (such as demographics by city, county state). That said you did a great job explaining the how/why of the SQL.
Thanks for the feedback.
really so best explanation😊😊
OK, how can I use the Distinct in between city and QUOTENAME
understand lot in Pivot Dynamic in one video
Thanks for the video. Very enlightening.
Thanks for the comment!
Solved my curiosity, thx
Glad to hear it, thanks for the comment!
I always got error with the statement below, I have no ideas why
A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.
Super helpful and much appreciated.
Really good video thanks for posting
Thanks for watching and commenting.
Amazing example for new bies
Is it possible to create a view that contains the resultset?
thank you very much! you really helped me.
How can I put two columns in Pivot?
how to make zero(0) if Average rent is null or how to use ISNULL function here
Check out the COALESCE() function.
@@AnthonySmoak sir this is my code SET @query = '
SELECT *
INTO #TempPivot
FROM (
SELECT DepartmentCode, '+ @cols+ '
FROM (
SELECT DepartmentCode, Label, isnull(Amount, 0) AS Amount
FROM #temp
UNION ALL
SELECT ''Total'', Label, SUM(Amount)
FROM #temp
GROUP BY Label
) AS ResultTable
PIVOT (
SUM(Amount)
FOR Label IN (' + @cols + ')
) AS PivotTable
) AS FinalResult;
-- Select from the temporary table
SELECT * FROM #TempPivot;
-- Drop the temporary table
DROP TABLE IF EXISTS #TempPivot;';
-- Execute the dynamic SQL query
EXEC sp_executesql @query;
but it gives this error
Incorrect syntax near the keyword 'COALESCE'.
pls can you help on this.
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
-- Get the distinct label values dynamically
SET @cols = STUFF(
(
SELECT DISTINCT ', COALESCE(' + QUOTENAME(Label) + ', 0) AS ' + QUOTENAME(Label)
FROM #temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1, 2, ''
);
sorry code this I missed
you are king, thank you..
My subscribers are royalty. Thanks for watching!
it is showing an error
Msg 8156, Level 16, State 1, Line 6
The column 'Feb 1 2018 12:00AM' was specified multiple times for 'Q'.
Msg 208, Level 16, State 0, Line 34
Invalid object name '##TBL_TEMP1'.
I have the same error,did you get it right?
@@magdelkgadimamabolo5560 Not Yet
Hey Anthony, great job in explaining the pivot function. I have a question to ask, it would be great if you could reply - How can we handle a scenario where the 'city' column would have more than 10,000 unique cities (let's assume) ?
well I mean, if it needs to be readable I'd use another reference like country or so. Maybe try to orient the query to a specific type of data like above certain numer?
Hi Anthony, I have a quick questions, how would you modify this query for a different scenario if there are two fields that needs to be filled in the Pivot for a single city, so for example let us assume 6118 and 6181 both should have rents assigned to sanfrancisco, if you see in your scenario no one city has got more than one rent filled in its rows
hello
I know it's been a long time ...
it is possible to leave all the data on the left ... I have a purpose to do it.
thanks for sharing
You want to exclude certain fields? If so, simply select what you need from the temp table that holds the pivot results.
Thank you!
Here, I don't want to display 'NULL' or '0' in the result, so that we can view a clear picture of the output. Can you please provide the query for it?
Try using the COALESCE function in a CASE statement to turn your NULL and 0 values into the empty string ''.
Or just use the Isnull function
thank you this helped me a lot :)
I need to run this in C#.. How can I do it?
Not sure about that one. That is out of my lane.
hi there lovely video however i am having a problem, by your method i am able to get the columns dynamically but problem is they are not unique and repeating value, just like it has combine all the row data in one cell, can you help out on this
It's difficult to diagnose with limited information. Some tweaking to the code may be warranted based upon your specific data set.
If you first Select Distinct on the column you are going to pivot and store it in a temp table IE #ColumnNames You can then use that in place of the (Cities) column in the pivot and you will get unique columns.
@@beadww Bradley could you show an example of this please
hello Sir I had a problem can you help me
Awesome..
How to handle that null value and replace as 0????
Experiment with the COALESCE() function.
@@AnthonySmoak Already done thanks for your response☺
Glad to hear it!
Cashville!
where did tbl_rent come from
World bank dataset, found it over 5 years ago somewhere.
Niceeeeee
What if city is repeated?
Then you would have a data quality issue with respect to this example.
@@AnthonySmoak okay
Please post table and data
omg even on a lot of rows, you can still make pivot table like this in excel with exactly 2 cliks…………..
Sure, but try doing this in Excel with 10 million rows. :)
How do we do the same Dynamic Pivot in SSIS...?
Use the Execute SQL task: learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task?view=sql-server-ver16
@@AnthonySmoak My source is Excel and the destination is OLE-DB...is it possible to Execute the SQL task.
If your data is in Excel, in order for SQL to work, you would need to import it into a relational database first. Excel is not a relational database. However, you can use the unpivot functionality in Power Query to get the same results as the SQL in this video. anthonysmoak.com/2017/07/16/easily-unpivot-your-data-in-excel-using-power-query/
@@AnthonySmoak Thank You ...
@@mathiyarasuelangovan2694 Good luck!