Time and time again I look for a video by you without doubt as my first choice! You have saved me many times over the years! Many blessings to you and yours kind sir!
A lot of the questions below were how you would make the query dynamic so that even if you don’t know how many columns you will get you can still run the query. I recommend reading chapter 11 of the book TSQL fundamentals 3rd edition. Which provided a solution for this. You would first select distinct rows of the new columns and store it into a cursor. create a dynamic query where you store the select pivot statement into a string cursor(with the parenthesis open). And use a while loop to add each column cursor into the pivot statement cursor until all distinct new columns have been added. You close the parenthesis after the last column was added. With += N’)). Then you can execute the query using EXEC.
Thank You for the great explanation, please find below the code for pivoting ( #pivoting ) with dynamic columns -- For dynamic columns -- set @DynamicPivotQuery with the final pivot query DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) --@ColumnName is used to store the maximum number of column names DECLARE @ColumnName AS NVARCHAR(MAX) -- Creating temporary table select * into #tmpCountry from (select country, city, 'City' + cast(row_number() over (partition by country order by country) as varchar(45)) as ColumnSequence from countries) Countries select @ColumnName= COALESCE(@ColumnName+ ', ','') + QUOTENAME(columnsequence) from ( select columnsequence from #tmpCountry where country = (select top 1 country from #tmpCountry group by country order by count(columnsequence) desc) ) test set @DynamicPivotQuery= 'select country, ' +@ColumnName+ 'from #tmpCountry PIVOT ( max(city) for ColumnSequence in (' +@ColumnName+ ') ) PIV' EXEC(@DynamicPivotQuery)
Awesome video, I had the same question last year and my developers gave me a blind faceless look when I proposed that they come up with a sql solution... Thank you..
I like this video and enjoyed watching and recommend others. City is hard coded I think we have to make automatically to create based on number of cities by concatenating 'City1' + 'City2' and so on Thanks for educating the community and appreciate your volunteership.
what happens when there are 1000 cities... considering this video will you right 1000 cities.. so please clear this doubt and make it dynamic and other things are good sir...
Thanks a Lot kudvenkat My question is their any other Way to make the fourth rows dynamic? Maybe sometimes you dont know how many rows you have! Best regards!
If you don't know how many cities there are going to be, it should work to add City4, City5, City6, etc. to the Select statement and the ColumnSequence statement. It should then give NULL values if the country does not have that many cities. This worked for me in a modified version of this code that I used to pivot a bunch of vital sign readings from rows into columns, with one row for each unique person with readings.
Hi Venkat, the videos really made a difference while developing my skills. I will be forever grateful to this series. I faced few difficulties while using Pivot functionality in PostGRE so I came up with this alternative, can you let me know if this works?- with base as ( select row_number() over (partition by country order by country) ran, country, city from countries ) select a.country,a.City1, b.City2, c.City3 from ( (select country, city as City1 from base where ran = 1 ) a join (select country, city as City2 from base where ran = 2 ) b on a.country=b.country join (select country, city as City3 from base where ran = 3 ) c on a.country=c.country )
Is there any way to automate the city name as well. Suppose we have 15 city for any country then we need to write 15 time city in selecta and pivot line. It should be automate so that based on the rownumber it will pick up the city
is it easier like below select country, max(case when rnk=1 then city end) over (partition by country) city_1, max(case when rnk=2 then city end) over (partition by country) city_2, max(case when rnk=3 then city end) over (partition by country) city_3 FROM ( select country, city, rank() over (partition by country order by city ) rnk FROM Countries ) abc
This is another great video. Guruji, I think before select statement it's better to build a string (strCity) and use the string in that case we will never alter the statements for every data inserted with new city What do you think? Lots of thanks for teaching
You are very welcome Sapna. I am very glad you are finding our courses useful. Thank you for the feedback. You can find our complete SQL course at the following link which I think could be useful as well. th-cam.com/play/PL08903FB7ACA1C2FB.html
hi venkat , i have a doubt in this video, when you did transformation from rows to columns, isn't that the violation of 1NF. because imagine if UK has only one city LONDON then the columns City2 and City 3 will violate the 1NF right?
Hello venkat sir why i am not getting the table . select * from(select Store,Week,xCount from yt)src pivot(sum(xCount) for Week in ([1], [2], [3]))piv; why isn't working is there any software problem .it's showing like -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql version' at line 1.
Hello Venkat, many of your videos in this series have blurry view , i can see other youtube videos fine but when i run yours in this series, it is blurry. Can you check that please?
I'm trying to do this query in Mysql but unfortunately mysql doesn't have pivot function , so is there any alternative way to create pivots using mysql
HI Sir, If we don not know the how many rows to be convert in to columns for ex. we know in this example 4 columns are city but if don't know cities than how can we do
Hi, Venket! I have a scenario in which i have column like products, year, months and salesvalue in rupees. now i want to pivot the records on year + month but problem is that I have about 25 - 30 column and may be it could more than in future. can we get the column dynamically. Thanks in advance! Sunil Kumar Kashyap
How can I do the opposite of what you did,I need to split ordersTransaction by products.(eg) If I have a single row that has this information [CustomerID]Customer102,[Product1]Burger,[Product2]OrangeJuice,[Product3]Fries ===================================== Transaction 1,Customer102,Burger(Product1) Transaction 2,Customer102,OrangeJuice(Product2) Transaction 3,Customer102,Fries(Product3) =====================================
Thanks sir.Very useful videos.I have question asked in interview.In 1 table contains columns deptid,deptname and another table empid,empname and deptid which is foreign key of first tabel(column deptid).Question was display department which has maximum employees.Please help to write this query
Hi Ashwini, very good question. Answering this question requires the knowledge of Joins and GroupBy. If you are new to those concepts, I recommend to watch Parts 11 & 12 from SQL Server Tutorial playlist. I will record and upload a video answering your question very soon. Thanks for asking. Good Luck.
Hi, Thank for this video, but after applying same logic I am not getting data in one row. It's coming in multiple rows with null and transpose data is not is single row.
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you th-cam.com/users/kudvenkatplaylists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. th-cam.com/video/y780MwhY70s/w-d-xo.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. th-cam.com/users/kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
Hello, I would be very grateful if someone help me with this. How do I do to get a pivot table, from a table that is constantly updating its fields or having new ones? Not this: select * from Table pivot (max (Field) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay where VendorId in ('ooo') but something like this: select *,("select of the updated query") from Table pivot (max (Field) for IncomeDay in ("here a select of the updated table") as MaxIncomePerDay where VendorId in ('ooo')
You are really amazing in teaching, I ever seen. Could you please help me for this example : CREATE TABLE OIVOT ( COL1 INT, COL2 NVARCHAR(20), COL3 NVARCHAR(20), ) INSERT INTO OIVOT VALUES (1,'TOM','HAPPY'),(1,'TIM','GOOD'),(2,'JACK','SAD'),(3,'JOHN','ANGRY'),(3,'JOHNY','HAPPY')
For dynamic numbers using stuff function DECLARE @CITY VARCHAR(500) = STUFF( (SELECT distinct ', ' + 'City' + cast(row_number() over(partition by Country order by Country) as varchar(10)) FROM Countries FOR XML PATH('') ),1,1,'' ) --SELECT @CITY declare @DynamicPivotQuery nvarchar(max) set @DynamicPivotQuery= 'Select Country, '+@CITY+' From ( Select Country, City, ''City''+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence from Countries ) Temp pivot ( max(City) for ColumnSequence in ('+@CITY+') ) Piv'
Questions: Create Normalize Structure Database of Job seeker Website With 3 Table Containing job_Seeker,Job_Seeker_Skill,Job_Seeker_Address Note that job Seeker Can Have Multiple Skill Set... Thanks In Advance
Please tell me how to write query below? Output column is calculated with adding Amt1 and Amt2 values to the Output Column value of previous record. Example: Output Column of first record is as it is of Amount column, second record will get from first record value of output column and amt1 , amt2 of first record i.e 100+0+10=110, third record is from 110+10+0=120 fourth record is from 120+0+0=120 fifth record is from 120+300+10=430 ... Input: dated amount amt1 amt2 1/1/2017 100 0 10 1/2/2017 100 10 0 1/4/2017 100 0 0 1/6/2017 100 300 10 1/10/2017 100 0 20 1/11/2017 100 350 650 1/12/2017 100 0 234 Output: dated amount amt1 amt2 Output Column 1/1/2017 100 0 10 100 1/2/2017 100 10 0 110 1/4/2017 100 0 0 120 1/6/2017 100 300 10 120 1/10/2017 100 0 20 430 1/11/2017 100 350 650 450 1/12/2017 100 0 234 1450 how to write query
PLEASE DON'T STOP THIS SERIES.
THIS IS GOING TO BE VERY USEFUL FOR THE BEGINNERS AND ALSO REFRESHING FOR PROFESSIONALS
Time and time again I look for a video by you without doubt as my first choice! You have saved me many times over the years! Many blessings to you and yours kind sir!
highly recommended. one of the best, if not the best, sql teacher i have ever encountered
Thank you very much. I’ve been using sql server for years and whenever I forget anything I know I will figure it out after reviewing your videos.
A lot of the questions below were how you would make the query dynamic so that even if you don’t know how many columns you will get you can still run the query. I recommend reading chapter 11 of the book TSQL fundamentals 3rd edition. Which provided a solution for this. You would first select distinct rows of the new columns and store it into a cursor. create a dynamic query where you store the select pivot statement into a string cursor(with the parenthesis open). And use a while loop to add each column cursor into the pivot statement cursor until all distinct new columns have been added. You close the parenthesis after the last column was added. With += N’)). Then you can execute the query using EXEC.
I could see this video prepared 7years ago, even it is so much helpful at 2021.. A big thanks for the simple explanation Ji 👍
Venka is one of the best teachers on this planet!
Thank You for the great explanation, please find below the code for pivoting ( #pivoting ) with dynamic columns
-- For dynamic columns
-- set @DynamicPivotQuery with the final pivot query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
--@ColumnName is used to store the maximum number of column names
DECLARE @ColumnName AS NVARCHAR(MAX)
-- Creating temporary table
select * into #tmpCountry
from
(select country, city, 'City' + cast(row_number() over (partition by country order by country) as varchar(45)) as ColumnSequence from countries) Countries
select @ColumnName= COALESCE(@ColumnName+ ', ','') + QUOTENAME(columnsequence)
from
(
select columnsequence from #tmpCountry
where country =
(select top 1 country from #tmpCountry
group by country
order by count(columnsequence) desc)
) test
set @DynamicPivotQuery=
'select country, ' +@ColumnName+ 'from #tmpCountry
PIVOT
(
max(city)
for ColumnSequence in (' +@ColumnName+ ')
) PIV'
EXEC(@DynamicPivotQuery)
ttansfomnb
just to check will this code loop through all the countries (as we are just selecting top 1 country in the internal query)
your explanation is so easy to understand. thank you for refreshing my mind about how pivot and partition by work 🙂
Awesome video, I had the same question last year and my developers gave me a blind faceless look when I proposed that they come up with a sql solution...
Thank you..
Thank you sir , the way of explanation is very simple and clear very easy to understand.
I like this video and enjoyed watching and recommend others.
City is hard coded I think we have to make automatically to create based on number of cities by concatenating 'City1' + 'City2' and so on
Thanks for educating the community and appreciate your volunteership.
Really Sir,
Incredible style of teaching! Thank you so much.
This is great I've worked with Pivot before but this really helps with getting the values unique to each column.
Awesome thanks! Worked great! On to the T-SQL Fundamentals book to make it dynamic.
This got solve one of my problem related to my project.. Thanks buddy!!
what happens when there are 1000 cities... considering this video will you right 1000 cities.. so please clear this doubt and make it dynamic and other things are good sir...
You have to make a stored procedure with declaration of 2 parameter ...for making it dynamic.
first time I saw concatenation of row_number with string using cast for row_number. Awesome logic
Many thanks Venkat..simply the best
Wow i tried a lot to solve this here ...your video really helped me :-)
Thanks a Lot kudvenkat My question is their any other Way to make the fourth rows dynamic? Maybe sometimes you dont know how many rows you have! Best regards!
If you don't know how many cities there are going to be, it should work to add City4, City5, City6, etc. to the Select statement and the ColumnSequence statement. It should then give NULL values if the country does not have that many cities.
This worked for me in a modified version of this code that I used to pivot a bunch of vital sign readings from rows into columns, with one row for each unique person with readings.
Exactly what I'd like to see, a dynamic solution for number of columns.
Hi Venkat, the videos really made a difference while developing my skills. I will be forever grateful to this series. I faced few difficulties while using Pivot functionality in PostGRE so I came up with this alternative, can you let me know if this works?-
with base as
(
select row_number() over (partition by country order by country) ran, country, city
from countries
)
select a.country,a.City1, b.City2, c.City3 from
(
(select country, city as City1
from
base
where ran = 1
) a
join
(select country, city as City2
from
base
where ran = 2
) b
on a.country=b.country
join
(select country, city as City3
from
base
where ran = 3
) c
on a.country=c.country
)
Great explanation and presentation voice
You are awsome! Thanks!!
Is there any way to automate the city name as well. Suppose we have 15 city for any country then we need to write 15 time city in selecta and pivot line. It should be automate so that based on the rownumber it will pick up the city
is it easier like below
select
country,
max(case when rnk=1 then city end) over (partition by country) city_1,
max(case when rnk=2 then city end) over (partition by country) city_2,
max(case when rnk=3 then city end) over (partition by country) city_3
FROM
(
select
country,
city,
rank() over (partition by country order by city ) rnk
FROM Countries
) abc
TEACHING is AWESOME
This is another great video.
Guruji,
I think before select statement it's better to build a string (strCity) and use the string in that case we will never alter the statements for every data inserted with new city
What do you think?
Lots of thanks for teaching
Awesome Tutorial
Thank you so much for this video. It helped a lot
You are very welcome Sapna. I am very glad you are finding our courses useful. Thank you for the feedback. You can find our complete SQL course at the following link which I think could be useful as well.
th-cam.com/play/PL08903FB7ACA1C2FB.html
hi venkat , i have a doubt in this video, when you did transformation from rows to columns, isn't that the violation of 1NF. because imagine if UK has only one city LONDON then the columns City2 and City 3 will violate the 1NF right?
Hello venkat sir why i am not getting the table .
select * from(select Store,Week,xCount from yt)src pivot(sum(xCount) for Week in ([1], [2], [3]))piv;
why isn't working
is there any software problem .it's showing like
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql version' at line 1.
Hello Venkat, many of your videos in this series have blurry view , i can see other youtube videos fine but when i run yours in this series, it is blurry. Can you check that please?
Hi venkat sir these videos are very helpful.But how can i do transformation of rows into columns using stored procedures.It's urgent sir.
Great Video... Thanks!
I'm trying to do this query in Mysql but unfortunately mysql doesn't have pivot function , so is there any alternative way to create pivots using mysql
Very useful.... Thank you....
Can you pls explain why we are using max(city) for the aggregation ?
Thts the syntax of pivot
Hi, what if the raw data has more than 2 columns (say 5 columns) and how to transpose it?
And What we do when we have unkown number of cities? Please it's urgent.
Have you got the answer, if yes, pls share.
HI Sir,
If we don not know the how many rows to be convert in to columns for ex. we know in this example 4 columns are city but if don't know cities than how can we do
Hi, Venket!
I have a scenario in which i have column like products, year, months and salesvalue in rupees.
now i want to pivot the records on year + month
but problem is that I have about 25 - 30 column and may be it could more than in future.
can we get the column dynamically.
Thanks in advance!
Sunil Kumar Kashyap
How can I do the opposite of what you did,I need to split ordersTransaction by products.(eg) If I have a single row that has this information
[CustomerID]Customer102,[Product1]Burger,[Product2]OrangeJuice,[Product3]Fries
=====================================
Transaction 1,Customer102,Burger(Product1)
Transaction 2,Customer102,OrangeJuice(Product2)
Transaction 3,Customer102,Fries(Product3)
=====================================
Thanks sir.Very useful videos.I have question asked in interview.In 1 table contains columns deptid,deptname and another table empid,empname and deptid which is foreign key of first tabel(column deptid).Question was display department which has maximum employees.Please help to write this query
Hi Ashwini, very good question. Answering this question requires the knowledge of Joins and GroupBy. If you are new to those concepts, I recommend to watch Parts 11 & 12 from SQL Server Tutorial playlist. I will record and upload a video answering your question very soon. Thanks for asking. Good Luck.
If we don’t know the values in the column ,how can we do the same?
Sir it is not working in SQL
thanks for useful information
is there a way to pivot multiple values, ex city AND zipcode?
Hi, Thank for this video, but after applying same logic I am not getting data in one row. It's coming in multiple rows with null and transpose data is not is single row.
thanks a lot guruji
really nice video please sir post a dynamic pivot
I am also waiting for the same..
awesome video on pivot....thanks
the above code is nt going to work if there is city4,5 etc...any code need to be dynamic
Thank you so mush for this video,
Sir can we achieve pivot result dynamically...?
what if i have different tables joined how can I implement this?
Than's very useful worked for me
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
th-cam.com/users/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
th-cam.com/video/y780MwhY70s/w-d-xo.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
th-cam.com/users/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
ɷ Heeyy Frienddssss I Have F0unddd W0rikinggggg Online Hacck visitt : - t.co/lwCJIlgsKa
Excellent
Thank you!
sir if have new delhi twice what will happen
write a querry to delete duplicates then pivot.
Nice Video.............
Can you please explain how to convert rows into columns without using pivot function ?
Thanks you!
thank u very much sir
why use MAX (city ) in Pivot part? thanks
sir y max is used ? max(city)?
Hello, I would be very grateful if someone help me with this.
How do I do to get a pivot table, from a table that is constantly updating its fields or having new ones?
Not this:
select * from Table
pivot (max (Field) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay
where VendorId in ('ooo')
but something like this:
select *,("select of the updated query") from Table
pivot (max (Field) for IncomeDay in ("here a select of the updated table") as MaxIncomePerDay
where VendorId in ('ooo')
Thanks
How to dynamically allocate column names?
You are really amazing in teaching, I ever seen.
Could you please help me for this example :
CREATE TABLE OIVOT
(
COL1 INT,
COL2 NVARCHAR(20),
COL3 NVARCHAR(20),
)
INSERT INTO OIVOT VALUES (1,'TOM','HAPPY'),(1,'TIM','GOOD'),(2,'JACK','SAD'),(3,'JOHN','ANGRY'),(3,'JOHNY','HAPPY')
excellent
For dynamic numbers using stuff function
DECLARE @CITY VARCHAR(500) = STUFF(
(SELECT distinct ', ' + 'City' + cast(row_number() over(partition by Country order by Country) as varchar(10))
FROM Countries FOR XML PATH('')
),1,1,''
)
--SELECT @CITY
declare @DynamicPivotQuery nvarchar(max)
set @DynamicPivotQuery=
'Select Country, '+@CITY+'
From
(
Select Country, City,
''City''+
cast(row_number() over(partition by Country order by Country)
as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in ('+@CITY+')
) Piv'
Questions: Create Normalize Structure Database of Job seeker Website With 3 Table Containing job_Seeker,Job_Seeker_Skill,Job_Seeker_Address Note that job Seeker Can Have Multiple Skill Set... Thanks In Advance
Sir showing error near as
Please tell me how to write query below?
Output column is calculated with adding Amt1 and Amt2 values to the Output Column value of previous record.
Example: Output Column of
first record is as it is of Amount column,
second record will get from first record value of output column and amt1 , amt2 of first record i.e 100+0+10=110,
third record is from 110+10+0=120
fourth record is from 120+0+0=120
fifth record is from 120+300+10=430 ...
Input:
dated amount amt1 amt2
1/1/2017 100 0 10
1/2/2017 100 10 0
1/4/2017 100 0 0
1/6/2017 100 300 10
1/10/2017 100 0 20
1/11/2017 100 350 650
1/12/2017 100 0 234
Output:
dated amount amt1 amt2 Output Column
1/1/2017 100 0 10 100
1/2/2017 100 10 0 110
1/4/2017 100 0 0 120
1/6/2017 100 300 10 120
1/10/2017 100 0 20 430
1/11/2017 100 350 650 450
1/12/2017 100 0 234 1450
how to write query