HomeОбразованиеRelated VideosMore From: kudvenkat

Part 6 Transform rows into columns in sql server

507 ratings | 199397 views
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/06/part-6-transform-rows-into-columns-in.html This is another common sql server interview question. We will be using Countries table in this example. SQL to create the table Create Table Countries ( Country nvarchar(50), City nvarchar(50) ) GO Insert into Countries values ('USA','New York') Insert into Countries values ('USA','Houston') Insert into Countries values ('USA','Dallas') Insert into Countries values ('India','Hyderabad') Insert into Countries values ('India','Bangalore') Insert into Countries values ('India','New Delhi') Insert into Countries values ('UK','London') Insert into Countries values ('UK','Birmingham') Insert into Countries values ('UK','Manchester') Here is the interview question. Write a sql query to transpose rows to columns. Using PIVOT operator we can very easily transform rows to columns. Select Country, City1, City2, City3 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 (City1, City2, City3) ) Piv
Html code for embedding videos on your blog
Text Comments (55)
Mike Stromme (1 month ago)
Awesome thanks! Worked great! On to the T-SQL Fundamentals book to make it dynamic.
nicolas rosso (4 months ago)
You are awsome! Thanks!!
Jason Ma (5 months ago)
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.
R P (6 months ago)
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?
krishnamohankumar (7 months ago)
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
Shaharukh Pathan (8 months ago)
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
Kumavat Jayesh (9 months ago)
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...
Ashwath Raj (11 months ago)
Can you pls explain why we are using max(city) for the aggregation ?
nice view (1 year ago)
Wow i tried a lot to solve this here ...your video really helped me :-)
Manik Manocha (1 year ago)
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, '[email protected]+' 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 ('[email protected]+') ) Piv'
DOMINILOCO (1 year ago)
what if i have different tables joined how can I implement this?
ramesh devalla (1 year ago)
sir if have new delhi twice what will happen
GSH RDY (1 year ago)
write a querry to delete duplicates then pivot.
Shweta Kumari (1 year ago)
Nice Video.............
harish raj (1 year ago)
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?
Lauren Dai (1 year ago)
why use MAX (city ) in Pivot part? thanks
gopi putta (2 years ago)
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.
gopi putta (2 years ago)
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.
Minakshi Das (2 years ago)
Can you please explain how to convert rows into columns without using pivot function ?
upendra kumar pandey (2 years ago)
really nice video please sir post a dynamic pivot
Sathish Kumar (1 year ago)
I am also waiting for the same..
Justin Li (2 years ago)
highly recommended. one of the best, if not the best, sql teacher i have ever encountered
Nitu Jaiswal (2 years ago)
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')
Johan Efendy (2 years ago)
Than's very useful worked for me
雲之 (2 years ago)
ɷ Heeyy Frienddssss I Have F0unddd W0rikinggggg Online Hacck visitt : - https://t.co/lwCJIlgsKa
kudvenkat (2 years ago)
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 https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=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
Mohammed Alkhafaf (3 years ago)
thanks for useful information
Kris Maly (3 years ago)
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.
Partha G (3 years ago)
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) [email protected] 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, ' [email protected]+ 'from #tmpCountry PIVOT ( max(city) for ColumnSequence in (' [email protected]+ ') ) PIV' EXEC(@DynamicPivotQuery)
Igor Casian (2 years ago)
Hector Munoz (3 years ago)
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..
Anirban De (3 years ago)
How to dynamically allocate column names?
Lekgotla Ramaite (3 years ago)
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) =====================================
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')
SAURABH TIWARI (4 years ago)
This got solve one of my problem related to my project.. Thanks buddy!!
Rishi Sharma (4 years ago)
Hi, This video is really helpful I am trying to do the reverse by having columns to rows eg current data set Date            Product_A_Qty           Product_B_Qty 02/10/15      100                             40 02/14/15       25                              50   What i am trying is to Date              Qty            Product 02/10/15        100              A                       02/10/15         40               B                       02/14/15         50               B   02/14/15         25               A  I would really appreciate a response. Thanks
Dattatray Pawar (4 years ago)
Hi Venkat, Its been really good videos for all of us to learn each and every bit of thing. I had come across one interview question about Pivot table. Q. What if upper column seems to dynamically generated? Sales1       Sales 2     Sales 3      Sales 4     Sales 5    UpTo    Sales 100 Mark         43434       43434          43434        43434        -         43434.4 Tom          32434       32453          32434        32434        -         32434.43 Thanks in advance Regards, Dattatray
Sunil Kashyap (4 years ago)
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
Kris Maly (4 years ago)
Kris Maly (4 years ago)
Kris Maly (4 years ago)
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
Debabrata Tripathy (4 years ago)
awesome video on pivot....thanks
Saagar Soni (4 years ago)
thank u very much sir
Almas Nas (4 years ago)
what if there is dynamic columns in which i have  large data and i don't know how many cities , how to control dynamic columns ?
Nirav Darji (4 years ago)
Really Sir, Incredible style of teaching! Thank you so much.
Ashwini Bandgar (4 years ago)
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
kudvenkat (4 years ago)
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.
ola Odusanya (4 years ago)
Many thanks Venkat..simply the best
Kamil Ibadov (4 years ago)
Thanks you!
Tarikffm (4 years ago)
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!
Wild One (1 year ago)
Exactly what I'd like to see, a dynamic solution for number of columns.
Adam Stevens (1 year ago)
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.
Tan YEW MENG (4 years ago)
Awesome Tutorial
Borka Siamese (4 years ago)
Thank you!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.