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

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

9 comments :

Krishna Kumar 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.

Abhishek Kumar Gupta 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

Give your Valuable Comments

Other Related Posts

© 2010-2012 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.