Excellent walkthrough of the building of the column list. The code is so cryptic the first time I read it somewhere else. The step-by-step extrication of the FOR XML PATH mode, the STUFF function, the value() method and the TYPE directive is superb. Please posting tutorials.
Hi there - thanks for all your great content! Do you by chance have a video for doing a dynamic pivot in a version of SQL that's 2017 or later? We're on 2019 so I am intrigued after your mention of the easier functions available....
@@BeardedDevData - thank you! I ended up watching that yesterday and saw it was covered there. I've used STRING_AGG before - a very handy function. I have a question about it, which I'll post on the other video. But a follow up question for this video -- when inserting a pivot table inside a larger query (i.e., among other CTEs), does the pivot need to happen at the very bottom, in the main query? Can one call the @Columns into the main query?
Oh - another question - if you want to replace the NULLs in the pivoted results with zeros, how (ISNULL? COALESCE? Something else?) and where is it best to do that?
That can be done after the PIVOT within the outer SELECT, technically you could do it before but if data doesn't exist, you would need to insert it, ISNULL or COALESCE will both work fine.
That's difficult to answer because it depends on your needs, you could do some data prep for example then perform a pivot operation on that data or you may need to do the pivot operation first then do some cleaning, filtering or maybe a combination, in terms of @Columns can use that anywhere, doesn't have to be pivot as long as its dynamic SQL.
Hey, that video and the explanation is absolutely great! Thanks a lot! Although everything works just fine I don’t understand why do we create the @query variable as a string :?
We need to use a valid data type for our parameter, in this case we are building the query, something of variable length that contains text. NVARCHAR(MAX) is overkill in this scenario.
Hi Thanks for the Video, I have some requirement, if not doing sum and Extending columns like monitor, monitor as monitor 1 (second Monitor column),monitor as monitor 2(3rd Monitor column) probably up to 10 such column's for monitor and similarly to other columns like laptop, pc, tablet (10 each). is there a way to perform such query
Hi Hima, apologies, I don't understand what you are trying to achieve and don't really want to guess, can you give me a bit more detail of what your data looks like and what you want it to look like, if its easier you can reach out to me at askbeardeddev@gmail.com.
thanks for your tutorial. But i have a problem. I can't run the code after i add the last row " EXEC sp_executesql @Query" but before. Can you help me ?
Hi great tutorial, I’m facing a slightly different situation. I’m creating a view, the problem I'm facing is that in a view I can’t declare any variables. I would like to know how to create dynamic rows that should be inside my "FOR IN()" statement. My view has a couple of CTEs but the last one is where I want my dynamic pivot. It's something like this. Thank you for your help CREATE VIEW [MyView] AS WITH CTE 1as( --- Query ) , SELECT * From CTE2( -- Select * From CTE1 ) , Select * From CTE2 PIVOT ( Max([Value]) For CodeName IN( -- this is what I currently have hard coded --- [HardCodeColumn 2], --- [HardCodeColumn 3], --- [HardCodeColumn 4], --- [HardCodeColumn 5], - - query or logic that should go here in order to get dynamic columns --that will output -- [column1] -- , [column2],….. ) )
Hey there, god bless your efforts in this channel. I am new to sql learner and has a simple enquiry. Is XML a file type like CSV or it is an integration tool? Thanks for taking care of my enquiry.
XML is similar to CSV in that both are information/data formats. CSV is simple and flat whereas XML is flexible and supports nesting. Both can be used for data integration.
My mind is being stretched. Great step-by-step instruction and demonstration, as usual. 👍
Brad: Do you encounter XQUERY often in your work?
Excellent walkthrough of the building of the column list. The code is so cryptic the first time I read it somewhere else. The step-by-step extrication of the FOR XML PATH mode, the STUFF function, the value() method and the TYPE directive is superb. Please posting tutorials.
Thanks so much 👍, I will be back soon.
Lots of good stuff in this cheers Dev 👍
Thanks Brian
Hi there - thanks for all your great content! Do you by chance have a video for doing a dynamic pivot in a version of SQL that's 2017 or later? We're on 2019 so I am intrigued after your mention of the easier functions available....
Sure, I cover a simpler version in part 2, ruclips.net/video/n9d8WSEBzAY/видео.html
@@BeardedDevData - thank you! I ended up watching that yesterday and saw it was covered there. I've used STRING_AGG before - a very handy function. I have a question about it, which I'll post on the other video. But a follow up question for this video -- when inserting a pivot table inside a larger query (i.e., among other CTEs), does the pivot need to happen at the very bottom, in the main query? Can one call the @Columns into the main query?
Oh - another question - if you want to replace the NULLs in the pivoted results with zeros, how (ISNULL? COALESCE? Something else?) and where is it best to do that?
That can be done after the PIVOT within the outer SELECT, technically you could do it before but if data doesn't exist, you would need to insert it, ISNULL or COALESCE will both work fine.
That's difficult to answer because it depends on your needs, you could do some data prep for example then perform a pivot operation on that data or you may need to do the pivot operation first then do some cleaning, filtering or maybe a combination, in terms of @Columns can use that anywhere, doesn't have to be pivot as long as its dynamic SQL.
Hey, that video and the explanation is absolutely great! Thanks a lot!
Although everything works just fine I don’t understand why do we create the @query variable as a string :?
We need to use a valid data type for our parameter, in this case we are building the query, something of variable length that contains text. NVARCHAR(MAX) is overkill in this scenario.
Hi Thanks for the Video, I have some requirement, if not doing sum and Extending columns like monitor, monitor as monitor 1 (second Monitor column),monitor as monitor 2(3rd Monitor column) probably up to 10 such column's for monitor and similarly to other columns like laptop, pc, tablet (10 each). is there a way to perform such query
Hi Hima, apologies, I don't understand what you are trying to achieve and don't really want to guess, can you give me a bit more detail of what your data looks like and what you want it to look like, if its easier you can reach out to me at askbeardeddev@gmail.com.
I want to sum up all the @columns in the product and also show a total of it. how I can do that?
Hi Gibson, check out this video, should cover what you need, ruclips.net/video/YPZwnb1WX9I/видео.html
thanks for your tutorial. But i have a problem. I can't run the code after i add the last row " EXEC sp_executesql @Query" but before. Can you help me ?
Sure, can you share the error message you are getting and the RDBMS you are using
Hi great tutorial, I’m facing a slightly different situation. I’m creating a view, the problem I'm facing is that in a view I can’t declare any variables. I would like to know how to create dynamic rows that should be inside my "FOR IN()" statement. My view has a couple of CTEs but the last one is where I want my dynamic pivot. It's something like this. Thank you for your help
CREATE VIEW [MyView] AS
WITH CTE 1as(
--- Query
) ,
SELECT * From CTE2(
-- Select * From CTE1
) , Select * From CTE2
PIVOT (
Max([Value])
For CodeName IN(
-- this is what I currently have hard coded
--- [HardCodeColumn 2],
--- [HardCodeColumn 3],
--- [HardCodeColumn 4],
--- [HardCodeColumn 5],
- - query or logic that should go here in order to get dynamic columns
--that will output
-- [column1]
-- , [column2],…..
)
)
As you can't declare variables within a view, you would need to create either a multi-valued table value function or a stored procedure.
Hey there, god bless your efforts in this channel.
I am new to sql learner and has a simple enquiry.
Is XML a file type like CSV or it is an integration tool?
Thanks for taking care of my enquiry.
XML is similar to CSV in that both are information/data formats. CSV is simple and flat whereas XML is flexible and supports nesting. Both can be used for data integration.
𝐩𝓻Ỗ𝓂Ø𝓈Ｍ 😥