Pivot - 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)

Pivot

Author

Topic

santosh2307
Starting Member

7 Posts

Posted - 2009-03-03 : 01:15:51

I want to do pivoting from a table output. I am generating a report as below.
Table :1

Customer due amount buckets
xyz 1000 1-5 days
abc 10000 21-30 days

The parameter sent to the report is ('1-5 days','6-10 days','11-20 days','21-30 days').

I want it to be as below

Table :2
Customer 1-5 days 6-10 days 11-20 days 21-30 days
xyz 1000
abc 10000

I tried with pivot operator in sql 2005 for table :1, but the buckets column does not contain all the intervals(1-5 days.......21-30 days) error is thrown,saying invalid column name 6-10 days ....

Please help to acheive this..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-03 : 01:26:48

declare @t table(Customer varchar(32), due int,amountbuckets varchar(32))
insert into @t select
'xyz', 1000 ,'1-5 days'
insert into @t select
'abc', 10000 ,'21-30 days'

select customer,[1-5 days],[6-10 days],[11-20 days],[21-30 days]
from
@T t
pivot (max(due) for amountbuckets in ([1-5 days],[6-10 days],[11-20 days],[21-30 days]))pnt

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 01:36:09

select customer,
max(case when amountbuckets = '1-5 days' then due else null end) [1-5 days],
max(case when amountbuckets = '6-10 days' then due else null end) [6-10 days],
max(case when amountbuckets = '11-20 days' then due else null end) [11-20 days],
max(case when amountbuckets = '21-30 days' then due else null end) [21-30 days]
from @t
group by customer
order by 1 desc

santosh2307
Starting Member

7 Posts

Posted - 2009-03-03 : 02:56:28

Thanks mate, thanks a lot...I did not write the code properly for the pivot operator...so was getting invalid column error...

Thanks..again

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-03 : 03:13:41

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 03:21:42

quote:
Originally posted by santosh2307

Thanks mate, thanks a lot...I did not write the code properly for the pivot operator...so was getting invalid column error...

Thanks..again

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources