Dynamically Pivot Data in SQL Server
HTML-код
- Опубликовано: 3 авг 2018
- In this video we will write T-SQL in SQL Server to build a dynamic query that will pivot a data set.
Watch the video and get the code here: anthonysmoak.com/2018/08/05/h...
★☆★ Join this channel to get access to perks: ★☆★
/ @anthonysmoak
★☆★ SUPPORT THIS CHANNEL: ★☆★
shop.spreadshirt.com/AnthonyS...
★☆★ FOLLOW ME BELOW: ★☆★
Blog ► anthonysmoak.com/
Twitter ► / anthonysmoak
Facebook ► /
Tableau Public ► public.tableau.com/profile/an...
#SQL #SQLServer #data #analytics #data #dataanalyst #education #onlinelearning
Great contents and examples, Loved it
Great job explaining PIVOT function, you were easy to listen to and understand
I appreciate it, thank you!
Super helpful and much appreciated.
This was a tremendous help to get my similar query to work. Thanks much!
Glad to hear it! Thanks for the comment.
Awesome video. Helped me get a clear grasp on pivots.
I appreciate the comment!
thank you very much! you really helped me.
thank you this helped me a lot :)
really so best explanation😊😊
Thanks for the video. Very enlightening.
Thanks for the comment!
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
Really good video thanks for posting
Thanks for watching and commenting.
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!
Awesome..
OK, how can I use the Distinct in between city and QUOTENAME
best pivot example on youtube
Thank you, I'm glad I was able to help!
Amazing example for new bies
you are king, thank you..
My subscribers are royalty. Thanks for watching!
You saved my days…. Thanks
Happy to help!
How can I put two columns in Pivot?
Is it possible to create a view that contains the resultset?
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.
Niceeeeee
Solved my curiosity, thx
Glad to hear it, thanks for the comment!
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?
hello Sir I had a problem can you help me
Cashville!
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
Please post table and data
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
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
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.
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
I need to run this in C#.. How can I do it?
Not sure about that one. That is out of my lane.
where did tbl_rent come from
World bank dataset, found it over 5 years ago somewhere.
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!
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!
What if city is repeated?
Then you would have a data quality issue with respect to this example.
@@AnthonySmoak okay
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. :)
This is very helpful. Thank you
Glad to hear it. Thanks!