Mysql – Insert data from multiple rows into multiple columns

MySQLpivot

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?

Best Answer

The question can be split into 3 main operations:

Partition rows by IndividualNumber (using variables)
Pivot Partitioned rows to columns (using CASE)
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
;

Related Solutions

Pivot rows into multiple columns

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |

Sql-server – Separate data from 1 column into multiple columns

CREATE TABLE #Data
    (
      ItemNo VARCHAR(10),
      Variant INT,
      SalesCode VARCHAR(30),
      UnitPrice INT
    )
GO
INSERT  INTO #Data
        ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES  ( 'A', 1, 'SRP', 100 ),
        ( 'A', 2, 'SRP', 200 ),
        ( 'A', 1, 'WSP', 50 ),
        ( 'A', 2, 'WSP', 100 ),
        ( 'B', 1, 'SRP', 300 ),
        ( 'B', 2, 'SRP', 400 ),
        ( 'B', 1, 'WSP', 150 ),
        ( 'B', 2, 'WSP', 200 )
GO
SELECT  ItemNo, Variant, 'SRP' AS SalesCode_SRP,
        SUM(CASE SalesCode
              WHEN 'SRP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
        SUM(CASE SalesCode
              WHEN 'WSP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_WSP
FROM    #Data
GROUP BY ItemNo, Variant 

GO
DROP TABLE  #Data