Rows to Column In Sql

Posted by Saranpselvam under Sql Server on 4/23/2014 | Points: 10 | Views : 443 | Status : [Member] | Replies : 4
If my Table look something like this

Create Table #Temp(Id Int, Name VarChar(20), Value VarChar(20))

Insert Into #Temp Values(1, 'Name','A')
Insert Into #Temp Values(1, 'ShirtSize','9.5')
Insert Into #Temp Values(1, 'Name','B')
Insert Into #Temp Values(1, 'ShirtSize','8.9')

Insert Into #Temp Values(2, 'Name','C')
Insert Into #Temp Values(2, 'ShoeSize','10.5')

Insert Into #Temp Values(3, 'Name','E')
Insert Into #Temp Values(3, 'ShirtSize','9')

Insert Into #Temp Values(4, 'Name','D')
Insert Into #Temp Values(4, 'ShirtSize','9')

and its output is like this

id | name : value
------------------

1 Name A

1 ShirtSize 9.5

1 Name B

1 ShirtSize 8.9

2 Name C

2 ShirtSize 10.5

I want my result in this format

id | Name | ShirtSize
-------------------

1 A 9.5

1 B 8.9

?2 C 10.5
3 D 9
4 E 9


Please Help Me




Responses

Posted by: Codesmoker on: 4/29/2014 [Member] Starter | Points: 25

Up
0
Down
Use PIVOT to change rows into columns
Refer this http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Saranpselvam, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Adsingh_Naz on: 5/14/2014 [Member] Starter | Points: 25

Up
0
Down
Saran,

For your requirement , There is no need of using PIVOT. It could be derived with simple join, Please check the below code

SELECT a.ID, a.Value AS Name, b.Value AS ShirtSize  
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Num
,ID, Name, Value
FROM #Temp
WHERE Name = 'Name'
) a
INNER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Num
,ID, Name, Value
FROM #Temp
WHERE Name <> 'Name'
) b
ON a.Num = b.Num


Saranpselvam, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Adsingh_Naz on: 6/17/2014 [Member] Starter | Points: 25

Up
0
Down
You can also refer this article to learn how to write pivot sql

http://dotnetbites.com/pivot

Saranpselvam, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 6/19/2014 [Member] Starter | Points: 25

Up
0
Down
Hi Saranpselvam,

For your requirement using pivot is little more complication process.
Try my cte query which may helpful for you.

;with cte1 AS (

select Id,Value Name,ROW_NUMBER()over(order by Id)Sn from #temp where name='Name'
),cte2 AS (
select Id,Value,ROW_NUMBER()over(order by Id)Sn from #temp where name<>'Name'
) select a.Id,a.Name,b.Value ShirtSize from cte1 a join cte2 b on a.Sn=b.Sn


Saranpselvam, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response