nesting and unnesting

Question

I have a table like below

name                value
-----        |      ------
rami         |      2
rami         |      3
rami         |      4

sam                 5
sam                 6

I want to piviot it in a way to get this result

Rami      sam
-----     -----
2,3,4     5,6

Please. Before posting verify the preview. What's more, show your efforts, your research, where you bump into an issue, and describe that particular aspect. Also tag which database engine you are using. — trincot, Aug 21 '20 at 21:34
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Aug 21 '20 at 21:39

score 1 · Answer 1 · answered Aug 21 '20 at 22:49

You need here a somewhat more complicated pivot table

the user defined variable @sql get the names and add the values The outer select is made by @sql2

CREATE TABLE name_val
    (`name` varchar(4), `value` int)
;
    
INSERT INTO name_val
    (`name`, `value`)
VALUES
    ('rami', 2),
    ('rami', 3),
    ('rami', 4),
    ('sam', 5),
    ('sam', 6)
;

SET @sql = NULL;
SET @sql2 = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('GROUP_CONCAT(IF(s.`name` = "', `name`,'", `value`,"")) AS ',name)
              ) INTO @sql
FROM name_val;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(', `name`,') AS ',name)
              ) INTO @sql2
FROM name_val;

SET @sql = CONCAT('SELECT ',@sql2,' FROM (SELECT ', @sql, ' 
                  FROM name_val s
                 GROUP BY s.`name`
                 ORDER BY s.`name`) t1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


rami  | sam
:---- | :--
2,3,4 | 5,6

db<>fiddle here

score 1 · Answer 2 · answered Aug 22 '20 at 12:21

1

Just use conditional aggregation:

select group_concat(case when name = 'Rami' then value end order by value) as rami,
       group_concat(case when name = 'Sam' then value end order by value) as sam
from t;

answered Aug 22 '20 at 12:21

Gordon Linoff

1,242,037
58
646
786

nesting and unnesting

2 Answers2