merge multiple rows into one with grouped records as new columns

Question

I have the following dataset:

╔════════════════════╦════════════════════╦═════════════╗
║id                  ║key                 ║Value        ║  
╠════════════════════╬════════════════════╬═════════════╣             
║1                   ║name                ║A            ║
║1                   ║country             ║UK           ║
║2                   ║name                ║Z            ║
╚════════════════════╩════════════════════╩═════════════╝

Trying this on MySQL 5.6

create table sample (
  `id` int,
  `key` varchar(255),
  `value` varchar(255)
  );
  
  insert into sample (`id`,`key`,`value`) values
  (1,'name', 'A'),
  (1,'country', 'UK'),
  (2,'name', 'Z');

I need a query to transform this data such that all the data for every id appears as one row with keys appearing as additional columns with respective values. The number of key-value pairs is known and fixed. (in this case, it would be 2)

╔════════════════════╦════════════════════╦═════════════╗
║id                  ║name                ║country      ║  
╠════════════════════╬════════════════════╬═════════════╣             
║1                   ║A                   ║UK           ║
║2                   ║Z                   ║null         ║
╚════════════════════╩════════════════════╩═════════════╝

Any help would be appreciated!

Hey @hsnsd, what have you tried so far? – Eduard Uta Aug 24 '21 at 11:20 — Eduard Uta, Aug 24 '21 at 11:20

score 1 · Accepted Answer · answered Aug 24 '21 at 11:19

1

One method is conditional aggregation:

select id,
       max(case when key = 'name' then value end) as name,
       max(case when key = 'country' then value end) as country
from sample
group by id;

answered Aug 24 '21 at 11:19

Gordon Linoff

1,242,037
58
646
786

Neat and sweet!! Couldn't think of adding the `max` in my existing query. – hsnsd Aug 24 '21 at 11:24

merge multiple rows into one with grouped records as new columns

1 Answers1