Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Convert Rows to Columns in SQL Server Without Using Pivot

Sep 15, 2012
Introduction:

Here I will explain how to convert row values to column values without using pivot in SQL Server.

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 RSS subscribe by email Subscribe by Email

13 comments :

Unknown said...

What if I have to decide the number of column at runtime?

Anonymous said...

It's really useful for me. Thank you Suresh.

Anonymous said...

this is really good but i am trying this when we have decide the number of columns at runtime like days in a month

Anonymous said...

Here columns are fixed needed for Dynamic columns.

Anonymous said...

This site is really use full to me,thank u suresh

Charles said...

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.

Unknown said...

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.

Anonymous said...

What if there are say, 15 rows, then we need to join 15 tables. So is there any alternative?

Anonymous said...

thank lot

Anonymous said...

how to achive this ?
Suresh mahesh Madhav
9999999999 9000000000 8125000000
8888888888 9005000000 8160000000
7777777777 ....... 8165555000

Unknown said...

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

Unknown said...

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


Anonymous said...

THANKS A LOT!!!!!

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.