SQLTeam.com | Weblogs | Forums

Unpivot columns to rows

Need Unpivot The following table

Id, r1,r2,r3,r4,r5,user ———column names
ID1,4,5,6,8,7,John. ———values

ID ,user, Reason
1 John 4. Which is r1 value
1 John 5. Which is r2. Value
1 John 6 Which is r3 value
1 John 8. Which is r4. Value

  1.  John.   7.  Which is r5 value
SELECT d.ID, reasons.reason, d.[user]
FROM #data d
CROSS APPLY ( VALUES(1,r1), (2,r2), (3,r3), (4,r4), (5,r5) ) AS reasons(reason#, reason)
ORDER BY d.ID, reasons.reason#
	if object_id('tempdb..#temp') is not null drop table #temp 

	select * 
	into #temp
	from (
			select 1 as 'Id',4 as 'r1', 5 as 'r2',6 as 'r3',8 as 'r4',7 as 'r5','John' as 'user'
	)Main



	select * from #temp
	unpivot( [Value] for [Columns] in ([r1],[r2],[r3],[r4],[r5]))unpvt