How Do Efficiently I Convert Dynamic Rows to Column in MySQL

Question

I'm looking for an efficient way to convert rows to columns in the SQL server, I heard that PIVOT is not very fast, and I need to deal with a lot of records.

I tried following on this Efficiently convert rows to columns in sql server but still not solved with my below example

This is my example: (updated)

   -----------------------------------------------
   | Id | Value  | ColumnName    | Submission_Id |
   -----------------------------------------------
   | 1  | John   | FirstName     | 1             |
   | 2  | 2.4    | Amount        | 1             |
   | 3  | ZH1E4A | PostalCode    | 1             |
   | 4  | Fork   | LastName      | 1             |
   | 5  | 857685 | AccountNumber | 1             |
   | 6  | Donny  | FirstName     | 2             |
   | 7  | 2.7    | Amount        | 2             |
   | 8  | ZH1E4C | PostalCode    | 2             |
   | 9  | Yen    | LastName      | 2             |
   | 10 | 857686 | AccountNumber | 2             |
   -----------------------------------------------

This is my expected result:

---------------------------------------------------------------------
| FirstName  |Amount|   PostalCode   |   LastName  |  AccountNumber |
---------------------------------------------------------------------
| John       | 2.4  |   ZH1E4A       |   Fork      |  857685        |
| Donny      | 2.7  |   ZH1E4C       |   Yen       |  857686        |
---------------------------------------------------------------------

How can I build the result?

How do you relate `LastName` = `Fork` is associated with `FirstName` = `John` and not with `FirstName` = `Donny ` ? and further you say `SQL server` in question but tagged `mysql` — Sujitmohanty30, Oct 07 '20 at 06:46
Your question has `mysql` tag and `PIVOT` operator is defined in `MS SQL`. mentioned pivot function has nothing to do with mysql — Pygirl, Oct 07 '20 at 06:56
Does MySQL even have any such functionality, slow or fast? Can you do it in the presentation layer instead? — jarlh, Oct 07 '20 at 07:10
Does this answer your question? [SQL pivot the column values](https://stackoverflow.com/questions/64068684/sql-pivot-the-column-values) — Pygirl, Oct 07 '20 at 08:07
@Nae The table already has submission_id so for `Jhon` has submission_id=1 and for `Donny` has 2 and so on — Muhammad Ilham, Oct 07 '20 at 08:07
The answer mentioned in the above question(posted by me) will be same in your case. — Pygirl, Oct 07 '20 at 08:08

marcothesane · Answer 1 · 2020-10-07T08:04:42.843

WITH
indata(Id,Value,ColumnName) AS (
          SELECT 1,'John'   ,'FirstName'
UNION ALL SELECT 2,'2.4'    ,'Amount'
UNION ALL SELECT 3,'ZH1E4A' ,'PostalCode'
UNION ALL SELECT 4,'Fork'   ,'LastName'
UNION ALL SELECT 5,'857685' ,'AccountNumber'
UNION ALL SELECT 6,'Donny'  ,'FirstName'
UNION ALL SELECT 7,'2.7'    ,'Amount'
UNION ALL SELECT 8,'ZH1E4C' ,'PostalCode'
UNION ALL SELECT 9,'Yen'    ,'LastName'
UNION ALL SELECT 10,'857686','AccountNumber'
)
,
-- need to get a grouping column, one that 
-- changes every time we encounter a 'FirstName
-- add a counter that is at 1 for FirstName
-- otherwise at 0, and build a running sum...
w_session_id AS (
  SELECT
    SUM(CASE ColumnName WHEN 'FirstName' THEN 1 END) 
    OVER(ORDER BY id) AS sessid
  , *
  FROM indata
)
-- now un-pivot manually 
SELECT
  sessid                                                   AS id
, MAX(CASE ColumnName WHEN 'FirstName'     THEN value END) AS FirstName
, MAX(CASE ColumnName WHEN 'Amount'        THEN value END) AS Amount
, MAX(CASE ColumnName WHEN 'PostalCode'    THEN value END) AS PostalCode
, MAX(CASE ColumnName WHEN 'LastName'      THEN value END) AS LastName
, MAX(CASE ColumnName WHEN 'AccountNumber' THEN value END) AS AccountNumber
FROM w_session_id
GROUP BY sessid;
-- out  id | FirstName | Amount | PostalCode | LastName | AccountNumber 
-- out ----+-----------+--------+------------+----------+---------------
-- out   1 | John      | 2.4    | ZH1E4A     | Fork     | 857685
-- out   2 | Donny     | 2.7    | ZH1E4C     | Yen      | 857686

score 1 · Accepted Answer · answered Oct 07 '20 at 08:55

You need to group them by their associative key, which you informed:

SELECT
  MAX(CASE ColumnName WHEN 'FirstName'     THEN Value END) AS FirstName,
  MAX(CASE ColumnName WHEN 'Amount'        THEN Value END) AS Amount,
  MAX(CASE ColumnName WHEN 'PostalCode'    THEN Value END) AS PostalCode,
  MAX(CASE ColumnName WHEN 'LastName'      THEN Value END) AS LastName,
  MAX(CASE ColumnName WHEN 'AccountNumber' THEN Value END) AS AccountNumber
FROM table
GROUP BY submission_id
;

GROUP BY enforces that there is single row for each unique submission_id, MAX selects the most descendant value of the expression for that particular group key (it is assumed to be singular so aggregate type should not matter), and finally CASE is filtering the Value by ColumnName.

How Do Efficiently I Convert Dynamic Rows to Column in MySQL

2 Answers2