Transpose data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Transpose data

Author

Topic

emarrero2005
Starting Member

2 Posts

Posted - 2014-02-14 : 07:26:39

Hi, I need to transpose data and could not find a way to do it even with pivot. There are a lot of example out there but I really could net make it work.

Here is a sample of the data I have:
ID Juan Carlos Alex
1 1500 750 850
2 250 1000 650
3 850 200 1000
4 150 1500 400

and this is the result I am looking for:
Names 1 2 3 4
Juan 1500 250 850 150
Carlos 750 1000 200 1500
Alex 850 650 1000 400

Tables have fixed quantity of Id's.

I will appreciate any help on this.
Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-14 : 08:35:57

[code]

;with aCTE
AS
(select 1 ID,1500 [Juan],750 [Carlos],850 [Alex] union all
select 2, 250, 1000, 650 union all
select 3, 850, 200, 1000 union all
select 4, 150, 1500, 400)

select *
from
(select * from aCTE) as pv
UNPIVOT
(Expanse FOR Name IN ([Juan],[Carlos],[Alex]) ) AS Unpvt

pivot
(sum(expanse) for ID in ([1],[2],[3],[4]) ) piv

[/code]

output
[code]
Name 1 2 3 4
Alex 850 650 1000 400
Carlos 750 1000 200 1500
Juan 1500 250 850 150
[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

emarrero2005
Starting Member

2 Posts

Posted - 2014-02-14 : 14:24:48

That was very nice. Appreciated.

Is there any way i can avoid the automatic sorting? I mean, I need the output in the same order as in my first post.

Thank you very much.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-15 : 01:46:37

add this at end of the script


Order by 1 desc

S

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources