move rows into columns - 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)

move rows into columns

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2011-01-28 : 02:39:02

Hello,

I'm using SQL 2005.
My table looks like this:

Acc|CUR|Amount
---|---|------
100|USD|22.10
101|AUD|200.34
101|EUR|33.12
102|AUD|224.45

I'd like to have the output as follows:

Acc|AUD |EUR |USD
---|------|-----|---
100|0.0000|0.000|22.10
101|200.34|33.12|0
102|224.45|0.000|0

Is that possible at all?
On searching the internet I read about crosstab reports? Would this be the right solution?
Also, I do not know how many different currencies are in the report so the currency columns would be dynamic if possible at all?

Any help much appreciated.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 02:44:27

Check the PIVOT!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 02:44:31

use dynamic SQL + PIVOT

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 02:57:26

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-01-28 : 03:26:53

create table #temp
(
Acc int,
CUR varchar(5),
Amount float
)

insert into #temp
select 100,'USD',22.10 union all
select 101,'AUD',200.34 union all
select 101,'EUR',33.12 union all
select 102,'AUD',224.45

SELECT Acc, AUD, EUR, USD
FROM
(
SELECT Acc, CUR, Amount
FROM #temp) AS source
PIVOT
(
SUM(Amount)
FOR CUR IN (AUD, EUR, USD)
) as pvt

IRK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-28 : 03:42:33

Ranjit,

For your query, the currency is fixed.
Wibni mention that

quote:
currency columns would be dynamic

KH
[spoiler]Time is always against us[/spoiler]

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-28 : 03:52:39

or

select Acc, isnull(AUD,0), isnull(EUR,0), isnull(USD,0) from #temp t1
pivot
(
sum(Amount) for cur in ( AUD, EUR, USD)
)p

sathish

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-28 : 05:22:12

quote:
Originally posted by sathishmangunuri

or

select Acc, isnull(AUD,0), isnull(EUR,0), isnull(USD,0) from #temp t1
pivot
(
sum(Amount) for cur in ( AUD, EUR, USD)
)p

sathish

Have you read previous replies?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources