TRANPOSE a column to ROW - 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 2005 Forums

Transact-SQL (2005)

TRANPOSE a column to ROW

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-27 : 10:16:06

So I have this table

ID    Term  Value
1      1Y     5
1      2Y     10
1      3Y     15

and I want to display it this way

ID    Value_1Y    Value_2Y    Value_3Y
1        5            10          15

A very simple SQL I can do is

SELECT ID, (SELECT VALUE FROM MYTABLE WHERE ID =1 AND TERM =1Y),
(SELECT VALUE FROM MYTABLE WHERE ID =1 AND TERM =2Y),
(SELECT VALUE FROM MYTABLE WHERE ID =1 AND TERM =3Y) FROM MYTABLE

This is very slow from what my friends told me. Is there a clever way to do this ?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 10:32:57

if there is only one "value" for each term & id combination and you know all possible term values you can use a pivot statement, similar to this...

declare @sample table (ID int not null, term char(2) not null, [value] int not null)

insert @sample
select 1,'1Y',5
union all select 1,'2Y',10
union all select 1,'3Y',15
union all select 2,'1Y',1
union all select 2,'2Y',2
union all select 2,'3Y',3

select [ID], [1Y] as [Value_1Y], [2Y] as [Value_2Y], [3Y] as [Value_3Y]
from
(
select [term], [ID], [value] from @sample
) as table_data
pivot
(
max([value])
for [term] in ([1Y], [2Y], [3Y])
) as pivot_table

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-27 : 10:39:40

Thanks,
The real table have ID column with hundred of different ids, not in any order.

The term column is the same for all id i.e each id has 1Y,2Y,3Y values.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-27 : 13:56:40

The pivot is only availabe on SQL 2005. Is there any alternative to do this on SQL 2000 ?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 15:52:57

the alternative is case statements :(

select
[ID]
,max(case when [term] = '1Y' then [value] else '' end)
,max(case when [term] = '2Y' then [value] else '' end)
,max(case when [term] = '3Y' then [value] else '' end)
from @sample
group by [ID]

P.S. you posted this in SQL 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:45:41

quote:
Originally posted by daman

The pivot is only availabe on SQL 2005. Is there any alternative to do this on SQL 2000 ?

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 03:10:19

see my post at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93312

it's basically the same question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources