Part 6 Transform rows into columns in sql server
HTML-код
- Опубликовано: 31 июл 2024
- Link for all dot net and sql server video tutorial playlists
/ kudvenkat
Link for slides, code samples and text version of the video
csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RUclips channel. Hope you can help.
/ @aarvikitchen5572
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
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!
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.
highly recommended. one of the best, if not the best, sql teacher i have ever encountered
Really Sir,
Incredible style of teaching! Thank you so much.
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 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.
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..
Many thanks Venkat..simply the best
This got solve one of my problem related to my project.. Thanks buddy!!
Awesome thanks! Worked great! On to the T-SQL Fundamentals book to make it dynamic.
I could see this video prepared 7years ago, even it is so much helpful at 2021.. A big thanks for the simple explanation Ji 👍
your explanation is so easy to understand. thank you for refreshing my mind about how pivot and partition by work 🙂
This is great I've worked with Pivot before but this really helps with getting the values unique to each column.
You are awsome! Thanks!!
Wow i tried a lot to solve this here ...your video really helped me :-)
TEACHING is AWESOME
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)
Venka is one of the best teachers on this planet!
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')
Awesome Tutorial
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?
Thank you!
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 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
thanks for useful information
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.
Thanks you!
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
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
Thank you sir , the way of explanation is very simple and clear very easy to understand.
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.
Great Video... Thanks!
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.
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.
first time I saw concatenation of row_number with string using cast for row_number. Awesome logic
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?
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.
ruclips.net/p/PL08903FB7ACA1C2FB
awesome video on pivot....thanks
Very useful.... Thank you....
thank u very much sir
what if i have different tables joined how can I implement this?
How to dynamically allocate column names?
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)
=====================================
If we don’t know the values in the column ,how can we do the same?
Great explanation and presentation voice
excellent
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
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
is there a way to pivot multiple values, ex city AND zipcode?
Can you pls explain why we are using max(city) for the aggregation ?
Thts the syntax of pivot
why use MAX (city ) in Pivot part? thanks
Hi, what if the raw data has more than 2 columns (say 5 columns) and how to transpose it?
Can you please explain how to convert rows into columns without using pivot function ?
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.
thanks a lot guruji
Excellent
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
ruclips.net/user/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.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/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
ɷ Heeyy Frienddssss I Have F0unddd W0rikinggggg Online Hacck visitt : - t.co/lwCJIlgsKa
And What we do when we have unkown number of cities? Please it's urgent.
Have you got the answer, if yes, pls share.
Thanks
Nice Video.............
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'
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.
really nice video please sir post a dynamic pivot
I am also waiting for the same..
Thank you so mush for this video,
Sir can we achieve pivot result dynamically...?
the above code is nt going to work if there is city4,5 etc...any code need to be dynamic
Sir it is not working in SQL
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')
sir if have new delhi twice what will happen
write a querry to delete duplicates then pivot.
sir y max is used ? max(city)?
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
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