Split Values into several other columns

Code :

CREATE TABLE table1 (
  day DATE,
  name VARCHAR(40)
  );
INSERT INTO table1 (day, name)
VALUES 
('2018-01-01', 'test1'),
('2018-01-01', 'test2'),
('2018-01-01', 'example'),
('2018-01-01', 'somevalue'),
('2018-01-02', 'test3'),
('2018-01-03', 'test4');

I want to split the result into something like :

day - name1 - name2 - namex  
DATE - value - value - value

instead of duplicating the date when I select data.

EXPECTED RESULT :

day - name - name - name - name ...
2018-01-01 - test1 - test2 - example - somevalue
2018-01-02 - NULL - NULL - NULL - NULL - test3

SQL FIDDLE

Solution

You can do this via dynamic sql, firstly finding the distinct name values then and building the rest of the code around them for example

given

MariaDB [sandbox]> select * from t;
+------------+-----------+
| day        | name      |
+------------+-----------+
| 2018-01-01 | test      |
| 2018-01-01 | test      |
| 2018-01-01 | example   |
| 2018-01-01 | somevalue |
| 2018-01-02 | test      |
| 2018-01-03 | test      |
+------------+-----------+
6 rows in set (0.00 sec)

set @sql = concat('select day, ',
(select group_concat(maxstr)
from
(select concat('max(case when name = ', char(39),name,char(39),' then  name else null end) as ', concat('name',@rn:=@rn+1)) maxstr
from  
(select distinct name from t) t,(select @rn:=0) rn
) s
)
,
' from t group by day')
;

builds this code

select day, max(case when name = 'test' then  name else null end) as name1,
        max(case when name = 'example' then  name else null end) as name2,
        max(case when name = 'somevalue' then  name else null end) as name3 
from t group by day;

Which yields this result when run

+------------+-------+---------+-----------+
| day        | name1 | name2   | name3     |
+------------+-------+---------+-----------+
| 2018-01-01 | test  | example | somevalue |
| 2018-01-02 | test  | NULL    | NULL      |
| 2018-01-03 | test  | NULL    | NULL      |
+------------+-------+---------+-----------+
3 rows in set (0.00 sec)

The advantage of using dynamic sql is that it's pretty much fire and forget any new values will be caught by the code. Be careful of the group_concat limit though.

Execute dynamic sql like this-

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;