Single record from Multiple child records via Select statement only
-
I need the ability to return a single record for a result set that may contain multiple children. Given:
ID SKU QTY 1 ABC 2 1 EFD 1 2 SFG 2 3 FGE 2 3 ABV 3 3 JKL 2 If need to produce the following:
ID SKU1 QTY1 SKU2 QTY2 SKU3 QTY3 1 ABC 2 EFD 1 Null Null 2 SFG 2 Null Null Null Null 3 FGE 2 ABV 3 JKL 2 The number of children may be more but would not exceed a defined limit. I've tried self joins but can't figure out the logic.
Any help is greatly appreciated!
-
It is called a pivot table and the problem you have is there is nothing to define which item will be SKU1, SKU2 and SKU3. Your best bet is to gen a temp table with your final table structure, then using a cursor go thru each if the records and your update clause check for the first NULL SKU and QTY field. Then at the end SELECT your temp table. Sorry no code on hand to post right off.
-
Here is the test case.
create table test(id int, sku varchar(5), qty int)
insert test values(1,'ABC',2)
insert test values(1,'EFD',1)
insert test values(2,'SFG',2)
insert test values(3,'FGE',2)
insert test values(3,'ABV',3)
insert test values(3,'JKL',2)
GO
select * from test
go
create function fnsku (@id int, @pos int)
returns varchar(5)
as
begin
declare @return varchar(5)
declare @stack table (pos int identity(1,1), sku varchar(5))
insert @stack (sku) select sku from test where id=@id order by sku
select @return = sku from @stack where pos = @pos
return @return
end
go
create function fnqty (@id int, @pos int)
returns int
as
begin
declare @return int, @sku varchar(5)
declare @stack table (pos int identity(1,1), sku varchar(5))
insert @stack (sku) select sku from test where id=@id order by sku
select @sku = sku from @stack where pos = @pos
select @return = qty from test where id = @id and sku = @sku
return @return
end
go
select id,
dbo.fnsku(id, 1) sku1, dbo.fnqty(id, 1) qty1,
dbo.fnsku(id, 2) sku2, dbo.fnqty(id, 2) qty2,
dbo.fnsku(id, 3) sku3, dbo.fnqty(id, 3) qty3
from test
group by id
go
drop function fnsku
drop function fnqty
drop table test
-
I just replied to the message prior to this one in the forum http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=49&messageid=107197
The solution I gave there should help you with this - set based, no cursors or temp tables. Your requirements are sort of similar.
You essentially do something like
select ID, (select qty from x where ID = outer.ID) as #1, (select qty from x where ID = outer.ID) as #2, etc
from myTable as [outer]
You would have to fix the, in your case, SKU codes, or generate dynamic SQL or a view that you update as required to return the appropriate columns. Read the link, it's explained better!
Good luck!!
-
I created a view that returned a record number for each sku within the group then modified your query to be based on this view and it works fine.
Thanks for the suggestion =:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply