Insert data from multiple rows into multiple columns

Question

I cannot find, after much searching, how this would be done.

My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The multiple-rows-to-single-row questions aren't helping me.

The problem:

There are Individuals in one table. There are Addresses (sometimes multiple ones) for those Individuals in another table. I need a query to put the multiple Addresses on a single row (in the appropriate column) for each Individual.

Here is a an MySQL Fiddle with the tables and the query:

• SQL Fiddle tables and query

In that SQL Fiddle the result has 9 records for 6 unique Individuals:

Number | Name | EyeColor | HairColor | Street | City | State | Zip | Street2 | City2 | State2| Zip2 | Street3 | ...
1 | John Smith | blue | red | 100 Pine Street | New York | NY | 10019 | | | | 0 | | ...
2 | Nancy Jones | green | red | 200 Pine Street | New York | NY | 10018 | | | | 0 | | ...
3 | Bobby Joe | blue | black | 310 Oak Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 100 Apple Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 200 Hickory Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 1234 Pineapple Street | New York | NY | 10018 | | | | 0 | | ...
2 | Nancy Jones | green | red | 230 Golden Street | New York | NY | 10018 | | | | 0 | | ...
8 | Sarah Shepard | brown | brown | (null) | (null) | (null) | (null)| (null) | (null)| (null)| (null)| (null) | ...

This is the SQL Fiddle with what I need the final result to be:

• MySQL Fiddle desired Final Result

The Final Result SQL Fiddle has 6 total records for the 6 unique Individuals and their multiple addresses in the columns on the Individual's row:

Number | Name | EyeColor | HairColor | Street | City | State | Zip | Street2 | City2 | State2| Zip2 | Street3 | City3 | State3 | Zip3 | Street4 | City4 | State4 | Zip4 | Street5 | City5 | State5 | Zip5 | Street6 | City6 | State6 | Zip6
1 | John Smith | blue | red | 100 Pine Street | New York | NY | 10019 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
2 | Nancy Jones | green | red | 200 Pine Street | New York | NY | 10018 | 230 Golden Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0 | | | | 0
3 | Bobby Joe | blue | black | 310 Oak Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
7 | Little Lebowski | green | blond | 100 Apple Street | New York | NY | 10018 | 200 Hickory Street| New York | NY | 10018 | 1234 Pineapple Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0
8 | Sarah Shepard | brown | brown | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
9 | Joe Profigliani | brown | brown | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0

I am working with the MySQL tables that I have been given, by the way, although open to ways to built temp tables for the solution, the question is about how to combine the data as shown in the final result, not about the elegance of the original tables.

There could be thousands of records, although I don't expect any one Individual to have more than 6 Addresses. (Having it fail gracefully if there were more than the Address fields available would be handy but that isn't the core of the question.)

I hope this is something simple that I am just not asking the right question for.

How are you making your decision as to which (name, address) combination is the "correct" one?

Well, I think that is may be the first 50% of my problem. All of the addresses are "correct" in that they all get written to the Individual's row. For example IndividualNumber 7 is LittleLebowski, He has 3 addresses, which all three should appear on that Individual's row in the Final Result. My assumption is that the first one to "appear" goes in as the first address. (Temp table with an auto-incremented value?) Once I answer that question about the order of the records, then the other 50% of the problem is to write them to the corresponding columns on that Individual's row?

Answer 1

The question can be split into 3 main operations:

1. Partition rows by IndividualNumber (using variables)
2. Pivot Partitioned rows to columns (using CASE)
3. Add Name and other information and remove NULL

You can look at the sample here: SQL Fiddle

Partition rows by IndividualNumber:

The behavior of this query is similar to the ROW_NUMBER() Window Function available in Oracle (>= 10g), PostgreSQL (>= 8.4) and SQL Server (>= 2012).

MySQL does not implement it and it must be done using variables and a CASE statement:

 SELECT @row := CASE WHEN inf.IndividualNumber = @id 
 THEN @row + 1 ELSE 1 END as row 
 , @id := inf.IndividualNumber as IndividualNumber
 , inf.IndividualAddressStreet
 , inf.IndividualCity
 , inf.IndividualState
 , inf.IndividualZip
 FROM (SELECT @row := 0, @id := 0) v
 , InfoaboutThemTable as inf 
 ORDER BY inf.IndividualNumber

It returns a unique row value from 1 to n for each IndividualNumber (partition) in InfoaboutThemTable:

row IndividualNumber IndividualAddressStreet IndividualCity IndividualState IndividualZip
1 1 100 Pine Street New York NY 10019
1 2 200 Pine Street New York NY 10018
2 2 201 Pine Street New York NY 10018
3 2 230 Golden Street New York NY 10018
4 2 456 Golden Street New York NY 10018
1 3 310 Oak Street New York NY 10018
1 7 100 Apple Street New York NY 10018
2 7 200 Hickory Street New York NY 10018
3 7 1234 Pineapple Street New York NY 10018 

Because you don't know yet how to order Address1, Address2, ... it only uses ORDER BY inf.IndividualNumber to partition by IndividualNumber in no specific order.

It could be replaced by ORDER BY inf.IndividualNumber, inf.IndividualAddressStreet if you were to partition by IndividualNumber and give a number to partition' members ordered by IndividualAddressStreet.

Pivot

Once each row for each partition in IndividualNumber has a unique row value, it can be used to transpose/pivot the rows to columns.

MySQL does not implement the PIVOT operator. Data can be move and pivoted to column X using a GROUP BY d.IndividualNumber and for each transposed columns, a CASE WHEN row = X THEN ... END is used with an aggregate (MAX):

MAX(CASE WHEN row = 1 THEN d.IndividualAddressStreet END) AS Street1
MAX(CASE WHEN row = 1 THEN d.IndividualCity END) AS City1
...
MAX(CASE WHEN row = 2 THEN d.IndividualAddressStreet END) AS Street2
...

It only contains 3 groups but you can easily expand it to 6 or N groups.

Add Name info:

Finaly IndividualsTable is LEFT JOIN to the pivoted subquery in order to add Names and Colors to the desired output.

NULL values are replaced by an empty string using COALESCE.

Full Query:

SELECT idt.IndividualNumber
 , idt.Name
 , idt.IndividualEyeColor
 , idt.IndividualHairColor
 , COALESCE(grp.Street1, '') as Street1
 , COALESCE(grp.City1, '') as City1
 , COALESCE(grp.State1, '') as State1
 , COALESCE(grp.Zip1, '') as Zip1
 , COALESCE(grp.Street2, '') as Street2
 , COALESCE(grp.City2, '') as City2
 , COALESCE(grp.State2, '') as State2
 , COALESCE(grp.Zip2, '') as Zip2
 , COALESCE(grp.Street3, '') as Street3
 , COALESCE(grp.City3, '') as City3
 , COALESCE(grp.State3, '') as State3
 , COALESCE(grp.Zip3, '') as Zip3
FROM IndividualsTable idt
LEFT JOIN (
 SELECT d.IndividualNumber as IndividualNumber
 , MAX(CASE WHEN row = 1 THEN d.IndividualAddressStreet END) AS Street1
 , MAX(CASE WHEN row = 1 THEN d.IndividualCity END) AS City1
 , MAX(CASE WHEN row = 1 THEN d.IndividualState END) AS State1
 , MAX(CASE WHEN row = 1 THEN d.IndividualZip END) AS Zip1
 , MAX(CASE WHEN row = 2 THEN d.IndividualAddressStreet END) AS Street2
 , MAX(CASE WHEN row = 2 THEN d.IndividualCity END) AS City2
 , MAX(CASE WHEN row = 2 THEN d.IndividualState END) AS State2
 , MAX(CASE WHEN row = 2 THEN d.IndividualZip END) AS Zip2
 , MAX(CASE WHEN row = 3 THEN d.IndividualAddressStreet END) AS Street3
 , MAX(CASE WHEN row = 3 THEN d.IndividualCity END) AS City3
 , MAX(CASE WHEN row = 3 THEN d.IndividualState END) AS State3
 , MAX(CASE WHEN row = 3 THEN d.IndividualZip END) AS Zip3
 FROM
 (
 SELECT @row := CASE WHEN inf.IndividualNumber = @id 
 THEN @row + 1 ELSE 1 END as row 
 , @id := inf.IndividualNumber as IndividualNumber
 , inf.IndividualAddressStreet
 , inf.IndividualCity
 , inf.IndividualState
 , inf.IndividualZip
 FROM (SELECT @row := 0, @id := 0) v
 , InfoaboutThemTable as inf 
 ORDER BY inf.IndividualNumber
 ) d 
 GROUP BY d.IndividualNumber
) grp
 ON grp.IndividualNumber = idt.IndividualNumber
;

• C0MPU7ER: Brilliant! Thank you Julien Vavasseur! Worked beautifully to solve a long-standing problem. What a great explanation broken into parts. I am relieved to see that this was indeed relatively complex to solve and that the solution involves both the Pivot and a method to Partition the rows. No wonder it wasn't easy to search for a solution. Thanks again!