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

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

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

[@Shashankgupta004]

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

[@sholland42]

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

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

[@vishalransing9531]

Superb explanation

[@LearnatKnowstar]

Thank you 🙂

[@navyavs7902]

How to do this in sql oracle 12g?

[@persianimmortal7642]

You explain sql query very meaningful

[@LearnatKnowstar]

Thank you

[@SandeepTiwari-yy4fg]

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

[@vidyak8638]

Can you please explain same query for Oracle sql

[@hv0032113]

You make it so easy to understand. Thank you.

[@LearnatKnowstar]

Thank you

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

[@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 - kzbin.info/www/bejne/l3WrZYtoo81sY5o

[@LearnatKnowstar]

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

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

[@petermaina7111]

You are a practically great

[@LearnatKnowstar]

Hope this was helpful.

[@alwayschandra]

Nice explanation and very easy to implement . Thank You

[@LearnatKnowstar]

Glad you liked it

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

[@francois7491]

Brilliant. Very helpful, thank you so much!

[@LearnatKnowstar]

Glad it was helpful!

[@miasp786]

How to sort the column using this query

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

[@StefanoVerugi]

excellent explanation, many thanks

[@LearnatKnowstar]

Thank you so much

[@johnghirardi]

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

[@LearnatKnowstar]

Glad it was helpful

[@tnmyk_]

Amazing explanation! Thank you very much!

[@LearnatKnowstar]

Glad it was helpful!

[@aswinkumar9751]

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

[@LearnatKnowstar]

Thank you

[@satyamsinha5429]

I have 1 question for pivoting

[@PrakashPraaku]

AMAZINGGGGG!! 👌👌👏👏

[@LearnatKnowstar]

Thanks!!

[@nimeshkotadia6526]

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

[@saket2006]

This is extremely helpful - thank you for sharing.

[@LearnatKnowstar]

Glad it was helpful.

[@hesam46]

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

[@saisubramanian6908]

excellent article and helped to move forward

[@LearnatKnowstar]

Thank you

[@zeeshanazeem5212]

Excellent Work. works like a charm,

[@LearnatKnowstar]

Glad it was helpful.

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

[@bhargavigogulamudi2758]

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

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

[@aneelt919]

Awesome.

[@LearnatKnowstar]

Thank you

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

[@sunilshinde9897]

Awesome.Thank you mam.its great Tut.

[@LearnatKnowstar]

Thank you

[@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 - kzbin.info/www/bejne/l3WrZYtoo81sY5o

[@shaiprabudhanasekaran7880]

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

[@sanjaysunilkumar3674]

very good tutorial

[@LearnatKnowstar]

Thank you!

[@ismaelobligacion7531]

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

[@melissawareham7429]

fabulous, very helpful, thank you!!

[@LearnatKnowstar]

Thank you so much!

[@ninhduongchan763]

which is the type of ''value''?

[@ninhduongchan763]

What is the 'value'?

[@LearnatKnowstar]

Value is a column name in the original table.

[@terryh558]

Awesome!😊

[@LearnatKnowstar]

Thank you 🙏

[@jeromevanbeek9070]

How do you do this with MySQL?

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

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

[@aadarshchaudhari3957]

Nice explanation. thank you so much

[@LearnatKnowstar]

Glad it was helpful.