Rows to Column In Sql

Posted by Saranpselvam under Sql Server on 4/23/2014 | Points: 10 | Views : 1614 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

0	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

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

Latest Posts