Unusual one-to-many relationship problem.

[mpdinosaur]

Folks;

I have what I think is an unusual problem with a one-to-many relationship query.

First some background. I have a table containing vending machine info. The other table contains one record for each product for sale in the machine. There may be one product, there may be 100 or any number in between.

My problem is this: I need to be able to extract the data so the machine data and up to five of the products are in a record. If there are more than five products, the next record should have the same machine data and products 6 thru 10 (the next five products, or less). etc.

It should look like this:

MachA Prod1 Prod2 Prod3 Prod4 Prod5
MachA Prod6 Prod7 Prod8
MachB Prod1 Prod2 Prod3 Prod4
MachC Prod1 Prod2 Prod3 Prod4 Prod5
MachC Prod6 Prod7 Prod8 Prod9 Prod10
MachC Prod11 Prod12

Does anyone have any ideas? I'm a 20-year vet, but new to SQL. Thanks in advance.

 

[swampBoogie]

Handle that in the program that displays the data. Retrieve the data ordered by Machine and keep track of when you have five products or a new machine.

SQL is not suitable.

 

[mpdinosaur]

Thanks. I suspected as much. Your answer now makes this a Crystal reports problem, I guess. I'm trying to print a "route card" for vending machines that need filling. The machine info need to print once with as many as five products printing on the card. I guess I should go to the CR forum.

Thanks again for your help.

 

[dnoeth]

This is just a modified version of a pivot query, so the main question is, which RDBMS?
If it supports SQL:1999 OLAP-functions (Orcale/IBM/Teradata) it's quite easy:

select
mach,
max(case when rn mod 5 = 0 then prod),
max(case when rn mod 5 = 1 then prod),
max(case when rn mod 5 = 2 then prod),
max(case when rn mod 5 = 3 then prod),
max(case when rn mod 5 = 5 then prod)
from
(select
mach,
prod,
row_number() over (partition by mach order by prod) - 1 as rn
from
mytable
) dt
group by
mach,
rn / 5

If ROW_NUMBER (or RANK or SUM OVER) is not supported have a look at a different thread with a solution for MSSQL:
thread220-746454