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.
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
@@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'.
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/
