Mysql – How to get multiple row data into a row with multiple columns

MySQLpivot

I have a MySQL table like this:

User_Id  course_name     course_location   course_id
1        course name 1   location 1        1
1        course name 2   location 2        2
1        course name 3   location 1        3
2        course name 2   location 1        2
2        course name 4   location 4        4

How can I get data a result like this:

User_id  course 1       course2        course3        course4
1        yes-location1  yes-location2  yes-location1  NULL
2        NULL           yes-location1  NULL           yes-location4

Best Answer

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.

Query:

SELECT User_id
    , MAX(
        CASE WHEN course_id = 1 THEN course_location END
    ) as Course_1
    , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
    , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
    , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.

You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.

Output:

User_Id | Course_1      | Course_2      | Course_3      | Course_4
1       | location 1    | location 2    | location 1    | (null)
2       | (null)        | location 1    | (null)        | location 4

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
;

Convert Two Rows into Two Columns in SQL Server

Here's a solution with a crosstab:

DECLARE @SampleData TABLE (
    Name varchar(50),
    Value varchar(50),
    Sequence int
)

INSERT INTO @SampleData
VALUES
 ('test'        ,'A1',         1)       
,('test'        ,'A2',         3)       
,('test2'       ,'A20',        5)       
,('test2'       ,'A10',        8);

WITH RankedData AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Sequence)
    FROM @SampleData
)
SELECT Name, 
    [Value+] = MAX(CASE WHEN RN = 1 THEN Value ELSE '' END),
    [Value-] = MAX(CASE WHEN RN = 2 THEN Value ELSE '' END)
FROM RankedData
GROUP BY Name

Best Answer

Query:

Output:

Related Solutions

MySQL Pivot – Insert Data from Multiple Rows into Multiple Columns

Partition rows by IndividualNumber:

Pivot

Add Name info:

Full Query:

Convert Two Rows into Two Columns in SQL Server

Related Question

Partition rows by `IndividualNumber`: