Splitting up multiple records into one row 1

[Brian1219]

This is a pretty simple question, but it's driving me nuts, and I need a little help. I've got a table:

column1: eid (unique) PK
column2: serial number
column3: Parts

There can be many duplicate serial numbers because there can be anywhere from 1-5 parts associated with a serial number.

What I would like to do is dump this information into a third table with the following format:

column1: serial number PK
column2-6: parts that associated with the serial number. There can be anywhere from 1-5, but there cannot be more than five.

Any ideas on how to accomplish this.

 

[rac2]

Hello Brian,

Here is an approach in two stages.

First, make a table2 like the original table with the addition of a column, part_counter, that counts the parts associated with each serial number. This could be done in a procedure with a cursor. Now the data in table2 is like this:

1 xyz 123 1
2 xyz 234 2
3 xyz 345 3
4 abc 123 1
5 abc 987 2

Then use this query to make a view or to insert rows into the final table

SELECT a.serial_number,
       p1.part AS "Part1",
       p2.part AS "Part2",
       p3.part AS "Part3",
       p4.part AS "Part4",
       p5.part AS "Part5"

FROM table2 a,
     table2 p1,
     table2 p2,
     table2 p3,
     table2 p4,
     table2 p5

WHERE      p1.serial_number = a.serial_number
      AND  p2.serial_number =* a.serial_number
      AND  p3.serial_number =* a.serial_number
      AND  p4.serial_number =* a.serial_number
      AND  p5.serial_number =* a.serial_number

 AND p1.part_counter = 1
 AND p2.part_counter = 2
 AND p3.part_counter = 3
 AND p4.part_counter = 4
 AND p5.part_counter = 5

The right-join will insure a row for every serial_number and is needed because most serial_numbers won't have five parts.

I have used this approach to transform a table of questionnaire answers with one answer per row to a table with all answers for one person in the same row. My data always had the same number of rows per person, so I have not tried the right-join piece.

Hope this idea leads you to a solution.

Richard

 

[Brian1219]

Thanks, you got me going in the right direction. Now I just need to learn how do properly execute a cursor.

I appreciate your help!