Introduction:
Description:
In
previous articles I explained Query to get duplicate records count, Query to remove duplicate records and many articles
relating to SQL
Server.
Now I will explain how to write a query to convert row values to column values
without pivot in SQL
Server.
In database I have one table UserInformation that contain data like as
shown below
 |
If
you observe above table we have name “Suresh”
which repeated for 3 times but it’s having different mobiles number in this
case I want to display single username with all mobiles numbers like as shown below
 |
To
implement this kind of functionality we need to write the query like as shown below
DECLARE @tempUserInfo TABLE
(UserName VARCHAR(50),Mobile VARCHAR(50))
INSERT INTO
@tempUserInfo VALUES ('Suresh',9999999999)
INSERT INTO
@tempUserInfo VALUES ('Suresh',8888888888)
INSERT INTO
@tempUserInfo VALUES ('Suresh',7777777777)
INSERT INTO
@tempUserInfo VALUES ('Mahesh',9000000000)
INSERT INTO
@tempUserInfo VALUES ('Mahesh',9005000000)
INSERT INTO
@tempUserInfo VALUES ('Madhav',8125000000)
INSERT INTO
@tempUserInfo VALUES ('Madhav',8160000000)
INSERT INTO
@tempUserInfo VALUES ('Madhav',8165555000)
;WITH UserDetails AS
( SELECT
*,ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserName) AS ColumnNumber
FROM @tempUserInfo
)
SELECT DISTINCT
t.UserName
,t1.Mobile AS website1,t2.Mobile AS website2,t3.Mobile AS website3
FROM @tempUserInfo t
LEFT OUTER JOIN UserDetails t1 ON
t.UserName=t1.UserName AND t1.ColumnNumber=1
LEFT OUTER JOIN UserDetails t2 ON
t.UserName=t2.UserName AND t2.ColumnNumber=2
LEFT OUTER JOIN UserDetails t3 ON
t.UserName=t3.UserName AND t3.ColumnNumber=3 Order by t.userName desc
|
Above query will change row values to column values
and the ouptput like as shown below
Output
|
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
13 comments :
What if I have to decide the number of column at runtime?
It's really useful for me. Thank you Suresh.
this is really good but i am trying this when we have decide the number of columns at runtime like days in a month
Here columns are fixed needed for Dynamic columns.
This site is really use full to me,thank u suresh
Hi Suresh, I stumbled upon ur post. i am looking for same logic. But my Sql query is within view. Hence i can't use insert to temp table to achieve this. Any alternative without using temp table ?
Thank U.
I have a table with data as follow
c aa
A1 H
A2 F
A3 H
B1 H
B2 F
B3 H
and I want record as
A1 A2 A3 B1 B2 B3
H F H H F H
I didnot know the exact number of row in the table.
What if there are say, 15 rows, then we need to join 15 tables. So is there any alternative?
thank lot
how to achive this ?
Suresh mahesh Madhav
9999999999 9000000000 8125000000
8888888888 9005000000 8160000000
7777777777 ....... 8165555000
Hi Guys, i think u r right the number of columns never remains the same but if u analyze the pattern is same, so depending on your cte you can construct a dynamic query that support N number of left join depending on the maximum count against any column mentioned in partition by column
HI.How to convert this type?
i have this type table
year amount city
2015 20 xxxx
2014 200 yyyy
2013 100 zzzzz
i want this type?
year_2015 Amount_2015 city_2015 year_2014 amount_2014 city_2014
2015 20 xxx 2014 200 yyy
THANKS A LOT!!!!!
Note: Only a member of this blog may post a comment.