September 26, 2009 at 7:16 pm
I have a question with the columns of a table transpose to rows
Customer AM/PM ShopA ShopB ShopC
Cust1 AM 10% 15% 25%
Cust1 PM 30% 10% 20%
Cust2 AM 10% 25% 23%
Cust2 PM 15% 17% 50%
this is what I need to have
Customer Shop AM PM
Cust1 ShopA 10% 30%
Cust1 ShopB 15% 10%
Cust1 ShopC 25% 20%
Cust2 ShopA 10% 15%
Cust2 ShopB 25% 17%
Cust2 ShopC 23% 50%
Any suggestions. Thank you.
September 26, 2009 at 10:55 pm
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
September 27, 2009 at 7:18 am
Thank you for the solution.
Is it possible to have an "UNPIVOT" alternate ?!
September 27, 2009 at 11:14 am
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
September 27, 2009 at 4:48 pm
Yes, Sir I got it.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply