February 27, 2006 at 10:33 pm
select item_id, location, qty
from balances
The above query gives following results
item idlocation qty
1234 mux 25
1234 lux 30
1234 abc 21
1234 cbe 15
5647 mux 1
5647 kuc 3
5647 jud 6
If I want the results as follows then what I have to do?
item id mux lux abc cbe kuc jud
1234 25 30 21 15
5647 1 3 6
February 28, 2006 at 9:07 am
Try this:
declare @Items table(ItemID int, Location char(3), Quantity int)
insert @Items values(1234,'mux',25)
insert @Items values(1234,'lux',30)
insert @Items values(1234,'abc',21)
insert @Items values(1234,'cbe',15)
insert @Items values(5647,'mux',1)
insert @Items values(5647,'kuc',3)
insert @Items values(5647,'jud',6)
select mux.ItemID
,mux.Quantity as 'mux'
,lux.Quantity as 'lux'
,abc.Quantity as 'abc'
,cbe.Quantity as 'cbe'
,kuc.Quantity as 'kuc'
,jud.Quantity as 'jud'
from @Items mux
left outer join @Items lux
on mux.ItemID = lux.ItemID and lux.Location = 'lux'
left outer join @Items abc
on mux.ItemID = abc.ItemID and abc.Location = 'abc'
left outer join @Items cbe
on mux.ItemID = cbe.ItemID and cbe.Location = 'cbe'
left outer join @Items kuc
on mux.ItemID = kuc.ItemID and kuc.Location = 'kuc'
left outer join @Items jud
on mux.ItemID = jud.ItemID and kuc.Location = 'jud'
where mux.Location = 'mux'
February 28, 2006 at 7:46 pm
declare @Items table(ItemID int, Location char(3), Quantity int)
insert @Items values(1234,'mux',25)
insert @Items values(1234,'lux',30)
insert @Items values(1234,'abc',21)
insert @Items values(1234,'cbe',15)
insert @Items values(5647,'mux',1)
insert @Items values(5647,'kuc',3)
insert @Items values(5647,'jud',6)
selectItemID,
max(case when Location = 'mux' then Quantity else 0 end) as [mux],
max(case when Location = 'lux' then Quantity else 0 end) as [lux],
max(case when Location = 'abc' then Quantity else 0 end) as [abc],
max(case when Location = 'cbe' then Quantity else 0 end) as [cbe],
max(case when Location = 'kuc' then Quantity else 0 end) as [kuc],
max(case when Location = 'jud' then Quantity else 0 end) as [jud]
from@Items
group by ItemID
March 1, 2006 at 10:45 am
For solution to pivoting/xtabs and much more check out RAC.Similar to Access crosstab but much more powerful with many features/options.And just as easy.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply