CODEWITHSUNDEEP
mysqlsql-serverunpivotmysql-8.0
jithin giri
jithin giri

Reputation: 743

Unpivot table in MySQL

I have a table like below

CREATE TABLE unpivot_table (
    FirstName varchar(100) NOT NULL, 
    LastName varchar(100) NOT NULL,
    query1 varchar(200) NOT NULL,
    result1 varchar(200) NOT NULL,
    query2 varchar(200) NOT NULL,
    result2 varchar(200) NOT NULL,
    query3 varchar(200) NOT NULL,
    result3 varchar(200) NOT NULL,
    query4 varchar(200) NOT NULL,
    result4 varchar(200) NOT NULL,
    query5 varchar(200) NOT NULL,
    result5 varchar(200) NOT NULL
    )

I am trying to unpivot the above table to look like the below table

CREATE TABLE UnPivoted (
        FirstName varchar(100) NOT NULL, 
        LastName varchar(100) NOT NULL,
        query varchar(200) NOT NULL,
        result varchar(200) NOT NULL
        )

In MSSQL I can achieve this by using the below query 

SELECT unpivot_table.FirstName, unpivot_table.LastName, 
        CrossApplied.query, CrossApplied.result
FROM unpivot_table
CROSS APPLY (VALUES (query1, result1),
                    (query2, result2),
                    (query3, result3),
                    (query4, result4),
                    (query5, result5)) 
            CrossApplied (query, result);

But i need this in MySQL and 'cross apply' which is used in this query is not in MySQL.

From my research I found 'lateral' is introduced by mysql as of MySQL 8.0.14 which can use as an alternative to 'cross apply' but i failed to write the query.

Please help to achieve this in MySQL.

Thanks in advance.

Upvotes: 1

Views: 589

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

mysql does not support Table Value constructor (VALUES). Only way to do this is to merge your query and result using union before doing cross join.

SELECT unpivot_table.FirstName, unpivot_table.LastName, 
        CrossApplied.query, CrossApplied.result
FROM unpivot_table
CROSS JOIN 
    (
      SELECT query1 as query, result1 as result FROM unpivot_table
      UNION ALL
      SELECT query2 as query, result2 as result FROM unpivot_table
      UNION ALL
      SELECT query3 as query, result3 as result FROM unpivot_table
      UNION ALL
      SELECT query4 as query, result4 as result FROM unpivot_table
      UNION ALL
      SELECT query5 as query, result5 as result FROM unpivot_table
    ) 
CrossApplied

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can use union all

select FirstName, LastName,query1, result1 from unpivot_table
union all
select FirstName, LastName,query2, result2 from unpivot_table
union all
select FirstName, LastName,query3, result3 from unpivot_table
union all
select FirstName, LastName,query4, result4 from unpivot_table

Upvotes: 3

Related Questions