February 27, 2006 at 2:57 am
example
Select item_id, location, qty
From Balances
Result
item_id location qty
1234 201 12
1234 301 10
1234 501 11
4567 201 22
4567 301 14
4567 401 21
But I want as follows
item id 201 301 401 501
1234 12 10 11
4567 22 14 21
how should I write the query?
February 27, 2006 at 3:52 am
You want something like this. I haven't tested this out but you should get the general idea.
create #a_temp_table
(item_id int)
declare @item_id int, @location int, @qty int, @new_location int
set @new_location = 0
declare a_cursor cursor
for
select item_id, location, qty
from Balances
order by item_id, location
open a_cursor
fetch next from a_cursor into @item_id, @location, @qty
while @@fetch_status = 0
begin
if @new_location @location
begin
set @new_location = @location
exec ('alter table #a_temp add [' + @new_location + '] int')
end
exec ('insert into #a_temp ([' + @new_location + '])
values (' + @quantity + ')')
fetch next from a_cursor into @item_id, @location, @qty
end
close a_cursor
deallocate a_cursor
select * from #a_temp
drop table #a_temp
February 28, 2006 at 10:40 am
Check out the Rac utility.Similar to Access Crosstab but much more powerful with many features/options.And just as easy to use.
February 28, 2006 at 11:38 am
Try something like this:
declare @Item table(ItemID int)
insert @Item values (1234)
insert @Item values (4567)
declare @Balance table(ItemID int, Location int, Quantity int)
insert @Balance values(1234,201,12)
insert @Balance values(1234,301,10)
insert @Balance values(1234,501,11)
insert @Balance values(4567,201,22)
insert @Balance values(4567,301,14)
insert @Balance values(4567,401,21)
select i.ItemID
,a.Quantity as '201'
,b.Quantity as '301'
,c.Quantity as '401'
,d.Quantity as '501'
from @Item i
left outer join @Balance a
on i.ItemID = a.ItemID and a.Location = 201
left outer join @Balance b
on i.ItemID = b.ItemID and b.Location = 301
left outer join @Balance c
on i.ItemID = c.ItemID and c.Location = 401
left outer join @Balance d
on i.ItemID = d.ItemID and d.Location = 501
February 28, 2006 at 7:40 pm
Also try something like this :
declare
@Balances table
(
item_idint,
locationint,
qtyint
)
insert into @Balances
select 1234, 201, 12union all
select1234, 301, 10union all
select1234, 501, 11union all
select4567, 201, 22union all
select4567, 301, 14union all
select4567, 401, 21
selectitem_id,
max(case when location = 201 then qty else 0 end) as [201],
max(case when location = 301 then qty else 0 end) as [301],
max(case when location = 401 then qty else 0 end) as [401],
max(case when location = 501 then qty else 0 end) as [501]
from@Balances
group by item_id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply