Combine all rows with the same name into a single row with multiple columns for the value-CodePudding

I have a MariaDB table with data like so:

Name    | Value
John    | 20    
Adrian  | 22   
Tommy   | 19  
John    | 24 
John    | 18
Adrian  | 23

It is to be returned like so:

Name    |Value1  |Value2   |Value3
John    |20      |24       |18
Adrian  |22      |23
Tommy   |19

We will not know how many value columns there could be. I would prefer the solution to be SQL only. Grateful for any form of help! :)

CodePudding user response：

Use group_concat

SELECT
   Name,
   GROUP_CONCAT(Value) 'Values'
FROM table_name
GROUP BY Name
ORDER BY Name;

Which will print all the values found in the second column.Its not exactly the presentation you asked for but it does give you the information in nearly the same format with a simple query. If you need to evaluate the data there are many standard functions that you could use including:

AVG(Value)
COUNT(Value)
MAX(Value)
MIN(Value)
AVG(Value)

The alternative is a procedure which will be more complicated.

CodePudding user response：

Is very hard to do in sql, because GROUP BY requires aggegate functions which distort the desired results. You could try make the column names the values and add a flag with a dynamic pivot.

Fiddle

create table t
(

  name varchar(10),
  value int
);

insert into t values
( 'John', 20),
( 'Adrian ', 22),
( 'Tommy ', 19),
( 'John ', 24),
( 'John ', 18),
( 'Adrian ', 23);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(name = ''',
      name,
      ''', 1, NULL)) AS `',
      value,'`'
    )
  ) INTO @sql
FROM t;

SET @sql = CONCAT('SELECT 
                    t.name,
         ', @sql, ' 
FROM t
              
GROUP BY name ORDER BY name');
                   

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

name    22  23  18  24  20  19
Adrian  1   1               
John            1   1   1   
Tommy                       1