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 🙏

[@johnghirardi]

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

[@LearnatKnowstar]

Glad it was helpful

[@hv0032113]

You make it so easy to understand. Thank you.

[@LearnatKnowstar]

Thank you

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

[@rajkotipatelgunishetti8813]

Outstanding ,it was clear as expected

[@LearnatKnowstar]

Glad it was helpful.

[@alwayschandra]

Nice explanation and very easy to implement . Thank You

[@LearnatKnowstar]

Glad you liked it

[@sholland42]

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

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

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

[@saket2006]

This is extremely helpful - thank you for sharing.

[@LearnatKnowstar]

Glad it was helpful.

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

[@tnmyk_]

Amazing explanation! Thank you very much!

[@LearnatKnowstar]

Glad it was helpful!

[@francois7491]

Brilliant. Very helpful, thank you so much!

[@LearnatKnowstar]

Glad it was helpful!

[@hesam46]

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

[@melissawareham7429]

fabulous, very helpful, thank you!!

[@LearnatKnowstar]

Thank you so much!

[@nimeshkotadia6526]

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

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

[@persianimmortal7642]

You explain sql query very meaningful

[@LearnatKnowstar]

Thank you

[@aadarshchaudhari3957]

Nice explanation. thank you so much

[@LearnatKnowstar]

Glad it was helpful.

[@StefanoVerugi]

excellent explanation, many thanks

[@LearnatKnowstar]

Thank you so much

[@saisubramanian6908]

excellent article and helped to move forward

[@LearnatKnowstar]

Thank you

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

[@vidyak8638]

Can you please explain same query for Oracle sql

[@navyavs7902]

How to do this in sql oracle 12g?

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

[@SandeepTiwari-yy4fg]

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

[@zeeshanazeem5212]

Excellent Work. works like a charm,

[@LearnatKnowstar]

Glad it was helpful.

[@aswinkumar9751]

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

[@LearnatKnowstar]

Thank you

[@miasp786]

How to sort the column using this query

[@PrakashPraaku]

AMAZINGGGGG!! 👌👌👏👏

[@LearnatKnowstar]

Thanks!!

[@bhargavigogulamudi2758]

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

[@vishalransing9531]

Superb explanation

[@LearnatKnowstar]

Thank you 🙂

[@petermaina7111]

You are a practically great

[@LearnatKnowstar]

Hope this was helpful.

[@sunilshinde9897]

Awesome.Thank you mam.its great Tut.

[@LearnatKnowstar]

Thank you

[@terryh558]

Awesome!😊

[@LearnatKnowstar]

Thank you 🙏

[@aneelt919]

Awesome.

[@LearnatKnowstar]

Thank you

[@jeromevanbeek9070]

How do you do this with MySQL?

[@satyamsinha5429]

I have 1 question for pivoting

[@shaiprabudhanasekaran7880]

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

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

[@ninhduongchan763]

which is the type of ''value''?

[@sanjaysunilkumar3674]

very good tutorial

[@LearnatKnowstar]

Thank you!

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

[@leolitvinsky4139]

order by is not allowed in subquery.

[@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 -
ruclips.net/video/aDH4X2nh9-8/видео.html

[@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 -
ruclips.net/video/aDH4X2nh9-8/видео.html

[@LearnatKnowstar]

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

[@ismaelobligacion7531]

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

[@ninhduongchan763]

What is the 'value'?

[@LearnatKnowstar]

Value is a column name in the original table.

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

[@ninhduongchan763]

why is the "value" blue?

[@sholland42]

It’s a SQL keyword

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

[@Hammersteyn]

what?