MYSQL: Merge datas with same id and put all the datas to another new column

Question

Good day,

I have a problem regarding how to build my query in MYSQL. I have this raw data.

ID       | NAME | VALUE | DESCRIPTION
ID-0001  | ARV1 | 10200 | First description
ID-0002  | ARV2 | 10300 | Second description
ID-0001  | ARV1 | 10400 | Added Description

And I want to achieve an output like this: Where I want to merge the datas with the same ID and put the other tables in another column and with new column name.

ID       | NAME | VALUE 1 | DESCRIPTION  1      |  VALUE 2 |  DESCRIPTION 2     |
ID-0001  | ARV1 | 10200   | First description   |  10400   |  Added Description |
ID-0002  | ARV2 | 10300   | Second description  |          |                    |

I badly need help. Thanks!

if many rows ID-0001 in this table then what will your table look like. Suppose your example where ID-0001 = 2 rows if it is 4 rows then — Rahul Biswas, Aug 25 '21 at 05:56
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — Kuro Neko, Aug 25 '21 at 05:57
Enumerate rows with the same ID in cte, then use 2 copies of it. PS. How to distinguish what data must be used for 1st columns pair and for 2nd one? — Akina, Aug 25 '21 at 06:06

Rahul Biswas · Accepted Answer · 2021-08-25T07:53:47.600

1

ID wise row is serialized as per storing data in DB that's why ORDER BY is not use. This query is alternative of PIVOT. But if more row exists for a same ID then PIVOT is better option. If any primary key is existed in table then use that PK at ORDER BY clause after PRTITION BY.

-- MySQL (v5.8)
SELECT t.id
     , MAX(t.name) name
     , MAX(CASE WHEN t.row_num = 1 THEN value END) value1
     , MAX(CASE WHEN t.row_num = 1 THEN description END) description1
     , MAX(CASE WHEN t.row_num = 2 THEN value END) value2
     , MAX(CASE WHEN t.row_num = 2 THEN description END) description2
FROM (SELECT *
           , ROW_NUMBER() OVER (PARTITION BY id) row_num
      FROM test) t
GROUP BY t.id;

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e4a5999a3536b9e042d7ed92fc392de7

Suppose table have a primary key column name p_id. Then ROW_NUMBER() will

ROW_NUMBER() OVER (PARTITION BY id ORDER BY p_id) row_num

N.B: By default p_id sorts the data in ascending order but if need descending order then use DESC.

edited Aug 25 '21 at 07:53

answered Aug 25 '21 at 06:08

Rahul Biswas

3,207
2
10
20

ORDER BY is mandatory when ROW_NUMBER is used with an OVER clause. – Madhukar Aug 25 '21 at 06:17
No ORDER BY IS NOT mandatory. Please check my given url – Rahul Biswas Aug 25 '21 at 06:17
@Madhukar *ORDER BY is mandatory* - no. But it provides definite ordering. – Akina Aug 25 '21 at 06:18
why negative vote for my answer. I don't totally understand that. – Rahul Biswas Aug 25 '21 at 06:19
@Akina and Rahul - Oh okay, May be it is mandatory only in SQL Server. – Madhukar Aug 25 '21 at 06:20
@Madhukar do you give negative vote for missing ORDER BY. – Rahul Biswas Aug 25 '21 at 06:22

score 1 · Answer 2 · answered Aug 25 '21 at 06:17

WITH 
cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY rowid) rn
    FROM test
)
SELECT t1.ID, t1.NAME, 
       t1.VALUE value1, t1.DESCRIPTION description1, 
       t2.VALUE value2, t2.DESCRIPTION description2
FROM cte t1
LEFT JOIN cte t2 ON t1.ID = t2.ID
                AND t1.NAME = t2.NAME
                AND t2.rn = 2
WHERE t1.rn = 1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e10ac4664ef614300c23a987f83e805d

PS. If a column which defines the rows priority not exists (rowid in the fiddle - it can be, for example, the creation datetime or something else) then the data order of the values pairs is not determined.

MYSQL: Merge datas with same id and put all the datas to another new column

2 Answers2