Pivot Issues

Hello,

So here's what I have in a table:

CoNo RentalIncome OtherIncome Insurance
1111 200 500 100
2222 300 400 50
3333 100 600 200
.... ... ... ...

...And here's what I need to do:

CoNo 1111 2222 3333 ....
RentalIncome 200 300 100 ...
OtherIncome 500 400 600 ...
Insurance 100 600 200 ...

I've played with a few pivots, but nothing so far is giving me exactly what I need (SUM aggregates bomb out with values like 'RentalIncome'). Can anyone point me to a good resource to check out?

Thanks in advance,
JasonThis is a bit out there, but I would try sticking the values in a temp table and then using a transformation matrix to swap the rows for columns, and vice versa.|||

Any chance of a demonstration? I couldn't come up with an elegant way of doing this (or non elegant for that matter that wasn't brute force)

|||

Actually with a little help from a friend, I did come up with a solution:

The basic idea used was to add a key for the rotate, and then do an UNPIVOT followed by a PIVOT. Note that I changed all of your datatypes to varchar(10). You will need like types to rotate the sets back and forth to get the output you want...

CoNo RentalIncome OtherIncome Insurance
1111 200 500 100
2222 300 400 50
3333 100 600 200
set nocount on
create table pivotTest
(
CoNo varchar(10) primary key,
RentalIncome varchar(10),
OtherIncome varchar(10),
insurance varchar(10)
)
insert into pivotTest (coNo, rentalIncome, Otherincome, insurance )
select '1111','200','500','100'
union all
select '2222','300','400','50'
union all
select '3333','100','600','200'
go

select *
from pivotTest
go
This returns:
CoNo RentalIncome OtherIncome insurance
- -- -
1111 200 500 100
2222 300 400 50
3333 100 600 200

First take the set and flatten it out:
with breakdown as( --cte instead of temp table or derived table
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select coNo as pkey, coNo, RentalIncome, OtherIncome, Insurance
from pivotTest) p
UNPIVOT
(value for name in (coNo, RentalIncome, OtherIncome, Insurance)) as unpvt)
select *
from breakdown
returns:
pkey name value
- -- -
1111 coNo 1111
1111 RentalIncome 200
1111 OtherIncome 500
1111 Insurance 100
2222 coNo 2222
2222 RentalIncome 300
2222 OtherIncome 400
2222 Insurance 50
3333 coNo 3333
3333 RentalIncome 100
3333 OtherIncome 600
3333 Insurance 200

Then rotate it with pivot on the pkey values (see --section repivot)
with breakdown as( --cte instead of temp table or derived table
--unpivot
select pkey, cast(name as varchar(20)) as name, value
from ( select coNo as pkey, coNo, RentalIncome, OtherIncome, Insurance
from pivotTest) p
UNPIVOT
(value for name in (coNo, RentalIncome, OtherIncome, Insurance)) as unpvt)

--repivot
select cast(name as varchar(10)) as Name, [1111] as col1 ,[2222] as col2,[3333] as col3
from
(select name, pkey,value
from breakdown) as rotated
PIVOT
(
MAX(value)
for pkey in ([1111],[2222],[3333])) as pvt
go

Name col1 col2 col3
- - - -
coNo 1111 2222 3333
Insurance 100 50 200
OtherIncom 500 400 600
RentalInco 200 300 100

--clean up
drop table pivotTest
go