MySQL show data from Rows to columns

join;MySQLpivot

I am stuck at getting a query with joining 2 tables. Below is the database structure.
What I want to achieve is to list all the information from one table with join on the second table but it has 3 conditions. I have written the following SQL but it's not working.I want only 2 rows returning all rows data in each 3 columns.
Please advice.

Tables are:

My SQL:

SELECT b.*,
       CASE WHEN pc.caster_type = '1' THEN pc.name END as  primary_caster ,
       CASE WHEN pc.caster_type = '2' THEN pc.name END as  secondary_caster ,
       CASE WHEN pc.caster_type = '3' THEN pc.name END as  caster  
FROM broadcasts b 
left join rel_braodcast_casters pc ON pc.fk_broadcast_id = b.broadcast_id ;

Getting this result:

I do not want multiple records for each broadcast. I want to replace NULL values with corresponding rows of data.

Thanks in advance.

Best Answer

Finally I got the answer. Below is the fiddle.

sqlfiddle.com/#!9/83f530/6 or sqlfiddle.com/#!9/83f530/7

Thanks to Akina who helped me a lot. I will prefer to use single join SQL over multiple join 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
;

MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property, 
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

SELECT
  p.id AS property_id, property, 
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')  
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle

Best Answer

Related Solutions

Mysql – Insert data from multiple rows into multiple columns

Partition rows by IndividualNumber:

Pivot

Add Name info:

Full Query:

MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

Related Question

Partition rows by `IndividualNumber`: