SQL Query | How to dynamically convert rows into columns | Dynamic Pivot

In this tutorial, we learn how to dynamically convert rows into columns using Dynamic pivoting in SQL Server.
How to convert rows into columns | Pivot
• SQL Query - Convert da...
How to use STRING_AGG function
docs.microsoft.com/en-us/sql/...
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Beginners to Python Programming
skillshare.eqcm.net/GjMakm
Data Science and Business Analytics with Python
skillshare.eqcm.net/JrM1Aq
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqcm.net/c/3299742...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - [e-mail]

[@LearnatKnowstar]

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

[@sylvia3820]

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 ❤️

[@LearnatKnowstar]

Glad it was helpful 🙏

[@rajkotipatelgunishetti8813]

Outstanding ,it was clear as expected

[@LearnatKnowstar]

Glad it was helpful.

[@hv0032113]

You make it so easy to understand. Thank you.

[@LearnatKnowstar]

Thank you

[@johnghirardi]

This was really useful and easy to follow. Thank you so much for sharing your knowledge! :)

[@LearnatKnowstar]

Glad it was helpful

[@melissawareham7429]

fabulous, very helpful, thank you!!

[@LearnatKnowstar]

Thank you so much!

[@francois7491]

Brilliant. Very helpful, thank you so much!

[@LearnatKnowstar]

Glad it was helpful!

[@tnmyk_]

Amazing explanation! Thank you very much!

[@LearnatKnowstar]

Glad it was helpful!

[@alwayschandra]

Nice explanation and very easy to implement . Thank You

[@LearnatKnowstar]

Glad you liked it

[@saket2006]

This is extremely helpful - thank you for sharing.

[@LearnatKnowstar]

Glad it was helpful.

[@StefanoVerugi]

excellent explanation, many thanks

[@LearnatKnowstar]

Thank you so much

[@zeeshanazeem5212]

Excellent Work. works like a charm,

[@LearnatKnowstar]

Glad it was helpful.

[@aadarshchaudhari3957]

Nice explanation. thank you so much

[@LearnatKnowstar]

Glad it was helpful.

[@PrakashPraaku]

AMAZINGGGGG!! 👌👌👏👏

[@LearnatKnowstar]

Thanks!!

[@saisubramanian6908]

excellent article and helped to move forward

[@LearnatKnowstar]

Thank you

[@terryh558]

Awesome!😊

[@LearnatKnowstar]

Thank you 🙏

[@vishalransing9531]

Superb explanation

[@LearnatKnowstar]

Thank you 🙂

[@petermaina7111]

You are a practically great

[@LearnatKnowstar]

Hope this was helpful.

[@aneelt919]

Awesome.

[@LearnatKnowstar]

Thank you

[@persianimmortal7642]

You explain sql query very meaningful

[@LearnatKnowstar]

Thank you

[@aswinkumar9751]

Great work madam.i really appreciate it ☺️☺️

[@LearnatKnowstar]

Thank you

[@sunilshinde9897]

Awesome.Thank you mam.its great Tut.

[@LearnatKnowstar]

Thank you

[@sholland42]

Nice presentation.
Try hitting ctrl-shift-r to refresh the intellisense cache and the red squigglies go away.

[@scvrive]

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

[@Anaya-un4ge]

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?

[@sanjaysunilkumar3674]

very good tutorial

[@LearnatKnowstar]

Thank you!

[@kamskamundu6720]

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.

[@haederoth]

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 !!!!

[@hesam46]

Thank you for this great video. How can I get the Demo database to replicate what you do?

[@sourabhpatil2361]

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?

[@nimeshkotadia6526]

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.

[@nimeshkotadia6526]

how to execute the @sql into a temporary table? Basically, I am trying add a column for Total.

[@navyavs7902]

How to do this in sql oracle 12g?

[@vidyak8638]

Can you please explain same query for Oracle sql

[@SandeepTiwari-yy4fg]

how to make zero(0) if salary is null or how to use ISNULL function here

[@haederoth]

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 !!!!

[@bhargavigogulamudi2758]

Can you please explain how to convert rows into columns dynamically in postgresql?

[@surik972]

Thanks a lot madam for all ur efforts!! Do u have any plans to start the oracle plsql concepts

[@LearnatKnowstar]

Glad it was helpful. We are definitely planning it in the future. Stay tuned and Subscribe 🙂

[@miasp786]

How to sort the column using this query

[@ninhduongchan763]

which is the type of ''value''?

[@shaiprabudhanasekaran7880]

Ma'am How to remove the space in between each Column Names.

[@shailendrakushwaha2130]

error while using string_agg function in sql server 2012: 'STRING_AGG' is not recognized built-in function name. pls help

[@LearnatKnowstar]

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 -
ua-cam.com/video/aDH4X2nh9-8/v-deo.html

[@LearnatKnowstar]

You can also check out this forum -
stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query

[@satyamsinha5429]

I have 1 question for pivoting

[@jeromevanbeek9070]

How do you do this with MySQL?

[@gregorybuiche5084]

Gregory Buiche
Nice explanation. Is there a way to make the same job without STRING_AGG for users of SQL SERVER 2014 😢

[@LearnatKnowstar]

Thank you. You can try using 'for xml' and stuff. It is explained in the below video -
ua-cam.com/video/aDH4X2nh9-8/v-deo.html

[@leolitvinsky4139]

order by is not allowed in subquery.

[@tusharjadhav6940]

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???

[@kartik-wi2jv]

Mam.. your personal audio added in between.. plz take care..

[@LearnatKnowstar]

Thank you for pointing it out. Reedited the video. Thank you.

[@kartik-wi2jv]

@@LearnatKnowstar thank you so much mam for sharing your knowledge..

[@ninhduongchan763]

What is the 'value'?

[@LearnatKnowstar]

Value is a column name in the original table.

[@ismaelobligacion7531]

Please comment here your whole query..,thank you,,

[@ninhduongchan763]

why is the "value" blue?

[@sholland42]

It’s a SQL keyword

[@capitalInd]

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

[@LearnatKnowstar]

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.

[@capitalInd]

@@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

[@TheRaju991]

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

[@Hammersteyn]

what?