Select statement

[md67]

I'm new to SQL and need help with a select statement. I have 2 tables one called item and the other called item_unit. I need to select all items, there is one record per item. And also select 1 field from the item unit file for each item. There can be 1 to 3 records for each item in the item_unit file. The link to the 2 files is item_number. I need one row returned for each item. Below is an example of what my files look like:

Item file:
Item_number Description
11111 Candy bar 1
11121 Candy bar 2
12332 Snack bar

Item_unit File:
Item_number Unit
11111 CS
11111 Box
11111 EA
11121 CS
11121 EA
12332 CS

I want my results set to look like this:
Item_number Unit1 Unit2 Unit3
11111 CS Box EA
11121 CS EA <null>
12332 CS <null> <null>

The item and item_unit files contain many more fields than I have described. I'm just trying to simplify my question. Any suggestions?

 

[Pattycake245]

I have posted code for a similar issue before. This uses cursors however:

create procedure dbo.maketable

AS

declare @count char(3), @count2 char(3), @SQLstring nvarchar(1000), @itemnum nvarchar(10), @itemcount int,
@insertlist nvarchar(1000), @unit nvarchar(10)

select item_number, count(*) "itemcount" into #item_unit from item_unit
group by item_number

select @count=max(itemcount) from #item_unit

set @count2=1
select @SQLstring = N'create table dbo.item_unit2(item_number nvarchar(10), '
while (@count2 < @count)
begin
select @SQLstring = @SQLstring + 'unit_'+ltrim(rtrim(@count2))+' nvarchar(10),'
select @count2 = @count2 + 1
end
if @count2 = @count
begin
select @SQLstring = @SQLstring + 'unit_'+ltrim(rtrim(@count2))+' nvarchar(10))'
end

execute sp_executesql @SQLstring

set @count2=0
declare insertcursor cursor for
select item_number, count(*) itemcount from item_unit group by item_number

open insertcursor
fetch next from insertcursor into @itemnum, @itemcount

while @@fetch_status =0
begin
declare valuescursor cursor for
select unit from item_unit where item_number = @itemnum

set @count2=1
select @insertlist = N'insert into dbo.item_unit2(item_number'
while (@count2 < @itemcount)
begin
select @insertlist = @insertlist + ',unit_'+ltrim(rtrim(@count2))+''
select @count2 = @count2 + 1
end
if @count2 = @itemcount
begin
select @insertlist = @insertlist + ',unit_'+ltrim(rtrim(@count2))+') values('''+@itemnum+''''
end

open valuescursor
fetch next from valuescursor into @unit

while @@fetch_status =0
begin
select @insertlist = @insertlist + ','''+@unit+''''
fetch next from valuescursor into @unit
end

close valuescursor
deallocate valuescursor
select @insertlist = @insertlist + ')'
execute sp_executesql @insertlist

set @insertlist=''
fetch next from insertcursor into @itemnum, @itemcount
end

close insertcursor
deallocate insertcursor


I don't see the use of the first table unless you are going to include the description in your final results or something.

Tim

 

[md67]

Thanks, I think a cursor will work but I was trying to stay away from a cursor. I've found that selects seem to be much faster and this is something that will be used quite frequetly.

Yes, I was planning on including the description and other fields from the item table. I was trying to keep my example as simple as possible.

I will probably go with the cursor if I can't find a better solution.

Thanks again.

Martha

 

[Pattycake245]

The solution without cursors was given in the attached thread, I haven't tested it though.

thread183-814041

 

[md67]

Sorry, I guess I didn't read it thoroughly.
Thanks!

 

[donutman]

My guess is that there is a very limited number of possible units. In that event an easier solution then previously posted is as follows:

Select I.ItemNumber,
Max(Case when Unit='CS' then Unit else Null end),
Max(Case when Unit='Box' then Unit else Null end),
Max(Case when Unit='EA' then Unit else Null end)
From ItemFile I inner join UnitFile U on I.ItemNumber=U.ItemNumber
Group By I.ItemNumber
Order By I.ItemNumber

If there are more Units then you need to add them to the list of Cases. BTW PattyCake, he said he was a beginner (that thread is pretty intimidating).
-Karl

 

[Pattycake245]

Donutman,

Maybe he shouldn't have been given this task if he is a beginner then. He is more than welcome to ask for a detailed description of what is going on if he wishes. I have answered his question. The code you have submitted, while simplified, is not at all dynamic, and if more units are added later, the code will have to be adjusted to accomodate more case statements every time this occurs. My code will never have to be changed.

Tim