Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022. The Coursera Plus membership gets you access to unlimited courses and unlimited certifications! imp.i384100.net/Ke51on
@LearnatKnowstar Thanks for sharing such an amazing method of pivoting rows into columns but i have a question here , what if we want to execute similar operation with columns to rows then how it would be possible as columns name are not stored anywhere which can be called in pivot function. Please advise.
thank you that was great , i want to combine fields from 2 more tables , lets say name was an ID to employee table andin the employee table there is a Dept ID linking to the department table , do you have another example i can watch to develop a multi table example
Hello. If someone can help --> What code lines need to be added in order to copy the result of this query into an other table of the database ? Many thanks !!!!
This is really good!! Thankyou for making it so easy to understand. I have a question. What if the Emp table is continuously getting new data every 15 mints. Would it be possible to do this without doing it from scratch everytime?
UP. If someone can help --> What code lines need to be added in order to copy the result of this query into an other table of the database ? Many thanks !!!!
Above method breaks down when i already have where condition with dates or names which already uses ' ' to convert to string. Any help around this would be helpful. Dynamic pivoting with where condition in the base query.
You need to try alternate approach. SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Name) FROM dbo.Empl1 c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') To understand how this works, you can check the below video tutorial - th-cam.com/video/aDH4X2nh9-8/w-d-xo.html
Hello mam , i have one string (my|name|is|tushar|abc|ahha|haha|hshs) I already separated it using create function which is similar like string_split because i have old version of server, So sir , i have table which contains 8 columns col1,col2,col,3 I have to insert the seprated string values into this columns(8 columns are their) I am seprated it but they are form rows My Name Is Tushar Like that i need to insert into columns or horizontal way. And its completely dynamic Can you help me out???
Declare @HOD Nvarchar(max); Declare @Sql Nvarchar(max); set @HOD= (select STRING_AGG([HOD Name],',') from (select distinct [HOD Name] from Dealer)a); Set @Sql = 'select' + @hod + 'from (Select [HOD Name] from Dealer) as Src_Table Pivot (count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' )) as pvt' execute (@sql) AFTER EXECUTE SHOW ERROR = Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ','. Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'as'. PLEASE HELP
@@capitalInd I know it's been a very long time. But there was a typo at this line with two closing parenthesis )) Pivot (count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' )) as pvt' so fix should have been only: Pivot (count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' ) as pvt'
Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on
Outstanding!! Was in a hurdle and thanks to you I was able to finish my report at work . Thank you so much for your teaching ❤️
Glad it was helpful 🙏
Outstanding ,it was clear as expected
Glad it was helpful.
You make it so easy to understand. Thank you.
Thank you
You explain sql query very meaningful
Thank you
excellent explanation, many thanks
Thank you so much
Nice presentation.
Try hitting ctrl-shift-r to refresh the intellisense cache and the red squigglies go away.
Nice explanation and very easy to implement . Thank You
Glad you liked it
Thanks very much. Then m'y question IS that how to join this pivot function with and other query in order to get opennning balance in report.
Brilliant. Very helpful, thank you so much!
Glad it was helpful!
excellent article and helped to move forward
Thank you
This was really useful and easy to follow. Thank you so much for sharing your knowledge! :)
Glad it was helpful
Great work madam.i really appreciate it ☺️☺️
Thank you
Amazing explanation! Thank you very much!
Glad it was helpful!
This is extremely helpful - thank you for sharing.
Glad it was helpful.
@LearnatKnowstar Thanks for sharing such an amazing method of pivoting rows into columns but i have a question here , what if we want to execute similar operation with columns to rows then how it would be possible as columns name are not stored anywhere which can be called in pivot function. Please advise.
Excellent Work. works like a charm,
Glad it was helpful.
Can you please explain same query for Oracle sql
How to do this in sql oracle 12g?
thank you that was great , i want to combine fields from 2 more tables , lets say name was an ID to employee table andin the employee table there is a Dept ID linking to the department table , do you have another example i can watch to develop a multi table example
Hello. If someone can help --> What code lines need to be added in order to copy the result of this query into an other table of the database ? Many thanks !!!!
Superb explanation
Thank you 🙂
Thank you for this great video. How can I get the Demo database to replicate what you do?
You are a practically great
Hope this was helpful.
This is really good!! Thankyou for making it so easy to understand. I have a question. What if the Emp table is continuously getting new data every 15 mints. Would it be possible to do this without doing it from scratch everytime?
AMAZINGGGGG!! 👌👌👏👏
Thanks!!
Awesome.Thank you mam.its great Tut.
Thank you
I have 1 question for pivoting
How to sort the column using this query
Thanks a lot madam for all ur efforts!! Do u have any plans to start the oracle plsql concepts
Glad it was helpful. We are definitely planning it in the future. Stay tuned and Subscribe 🙂
very good tutorial
Thank you!
Can you please explain how to convert rows into columns dynamically in postgresql?
fabulous, very helpful, thank you!!
Thank you so much!
how to make zero(0) if salary is null or how to use ISNULL function here
UP. If someone can help --> What code lines need to be added in order to copy the result of this query into an other table of the database ? Many thanks !!!!
how to execute the @sql into a temporary table? Basically, I am trying add a column for Total.
Above method breaks down when i already have where condition with dates or names which already uses ' ' to convert to string. Any help around this would be helpful. Dynamic pivoting with where condition in the base query.
error while using string_agg function in sql server 2012: 'STRING_AGG' is not recognized built-in function name. pls help
You need to try alternate approach.
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Name)
FROM dbo.Empl1 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
To understand how this works, you can check the below video tutorial -
th-cam.com/video/aDH4X2nh9-8/w-d-xo.html
You can also check out this forum -
stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query
Awesome!😊
Thank you 🙏
Gregory Buiche
Nice explanation. Is there a way to make the same job without STRING_AGG for users of SQL SERVER 2014 😢
Thank you. You can try using 'for xml' and stuff. It is explained in the below video -
th-cam.com/video/aDH4X2nh9-8/w-d-xo.html
Mam one Question
I we want one particular name at top and other remaining names down the top name then what is query for that?
which is the type of ''value''?
Ma'am How to remove the space in between each Column Names.
Please comment here your whole query..,thank you,,
Awesome.
Thank you
order by is not allowed in subquery.
What is the 'value'?
Value is a column name in the original table.
How do you do this with MySQL?
Mam.. your personal audio added in between.. plz take care..
Thank you for pointing it out. Reedited the video. Thank you.
@@LearnatKnowstar thank you so much mam for sharing your knowledge..
Hello mam , i have one string (my|name|is|tushar|abc|ahha|haha|hshs)
I already separated it using create function which is similar like string_split because i have old version of server,
So sir , i have table which contains 8 columns col1,col2,col,3
I have to insert the seprated string values into this columns(8 columns are their)
I am seprated it but they are form rows
My
Name
Is
Tushar
Like that i need to insert into columns or horizontal way.
And its completely dynamic
Can you help me out???
why is the "value" blue?
It’s a SQL keyword
Declare @HOD Nvarchar(max);
Declare @Sql Nvarchar(max);
set @HOD=
(select STRING_AGG([HOD Name],',') from
(select distinct [HOD Name] from Dealer)a);
Set @Sql =
'select' + @hod + 'from
(Select [HOD Name] from Dealer) as Src_Table
Pivot
(count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' )) as pvt'
execute (@sql)
AFTER EXECUTE SHOW ERROR = Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'as'.
PLEASE HELP
You can try printing @sql text and check for any syntax errors. It could be due to missing spaces around the @col values that you have inserted.
@@LearnatKnowstar
printing @sql text working fine but giving error while executing @sql
check printing @sql text
select Aashish .,Ajit Kumar Singh,Anup Kumar Chaudhary,Ashutosh Goutam,Bhavesh R Kanadiya,Brijender Kumar,Deepak Chaudhary,Gautam Singh,Juned Niyaj Ahmad,Madhu . Kashyap,Mayur Tarachand Purohit,Mohammad Khalid,Mohd Danish Shaikh,Mukesh Kumar Ray,Pankaj Kumar Singh,Piyusha Satish Kotkar,Ritesh Kumar Sinha,Sani Kumar Kanojia,Suman Kumar,Tahseen Raza Khan,Tribhuwan Narayan Pandey,Varun Ashok Sharma,Vivek Kushwahafrom
(Select [HOD Name] from Dealer) as Src_Table
Pivot
(count([HOD Name]) for [HOD Name] in ( Aashish .,Ajit Kumar Singh,Anup Kumar Chaudhary,Ashutosh Goutam,Bhavesh R Kanadiya,Brijender Kumar,Deepak Chaudhary,Gautam Singh,Juned Niyaj Ahmad,Madhu . Kashyap,Mayur Tarachand Purohit,Mohammad Khalid,Mohd Danish Shaikh,Mukesh Kumar Ray,Pankaj Kumar Singh,Piyusha Satish Kotkar,Ritesh Kumar Sinha,Sani Kumar Kanojia,Suman Kumar,Tahseen Raza Khan,Tribhuwan Narayan Pandey,Varun Ashok Sharma,Vivek Kushwaha )) as pvt
PLZ HELP HOW TO RESOLE THIS ERROR
@@capitalInd I know it's been a very long time. But there was a typo at this line with two closing parenthesis ))
Pivot
(count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' )) as pvt'
so fix should have been only:
Pivot
(count([HOD Name]) for [HOD Name] in ( ' + @HOD + ' ) as pvt'
what?
Nice explanation. thank you so much
Glad it was helpful.