un-pivot feature applying

Question

Post reply

un-pivot feature applying

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

No promises about performance with this solution.

declare @sample table (Customer char(5), [AM/PM] char(2), ShopA int, ShopB int, ShopC int)

insert into @sample

select 'Cust1','AM',10, 15, 25 union all

select 'Cust1','PM',30, 10, 20 union all

select 'Cust2','AM',10, 25, 23 union all

select 'Cust2','PM',15, 17, 50

select * from @sample

-- This is crude but it works. The downside is three scans through the source table.

;with cte as

(select customer,'ShopA' as Shop

,case when [AM/PM] = 'AM' then ShopA else 0 end as AM

,case when [AM/PM] = 'PM' then ShopA else 0 end as PM

from @sample

union all

select customer,'ShopB' as Shop

,case when [AM/PM] = 'AM' then ShopB else 0 end as AM

,case when [AM/PM] = 'PM' then ShopB else 0 end as PM

from @sample

union all

select customer,'ShopC' as Shop

,case when [AM/PM] = 'AM' then ShopC else 0 end as AM

,case when [AM/PM] = 'PM' then ShopC else 0 end as PM

from @sample

)

select customer,shop,max(AM) as AM, max(PM) as PM

from cte

group by customer,shop

order by customer,shop

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 2

Thank you for the solution.

Is it possible to have an "UNPIVOT" alternate ?!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Yes, and it gets the job done with only one pass through the source table.

-- unpivot only

SELECT Customer,Shop

,max(case when [AM/PM] = 'AM' then PCT else null end) as AM

,max(case when [AM/PM] = 'PM' then PCT else null end) as PM

FROM

(SELECT Customer, [AM/PM], ShopA, ShopB, ShopC

FROM @sample) AS p

UNPIVOT

(Pct FOR Shop IN

(ShopA, ShopB, ShopC)

)AS unpvt

GROUP BY Customer,Shop

ORDER BY Customer,Shop

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

sqlgreenhand SSCommitted Points: 1639 More actions · Answer 4

sqlgreenhand

SSCommitted

Points: 1639

September 27, 2009 at 4:48 pm

#1058876

Yes, Sir I got it.

Thanks.