mysql - How To use multiple columns

Question

id  day  line    color   
1   1    1       white          
2   1    2       orange    
3   2    1       White       
4   2    2       Orange     
5   2    3       White  

I'm trying to get a result :

day line  color         
1   1,2    white,orange 
2   1,2,3    white,orange,white 

SELECT day
     , line
     , color  from name_table 
  GROUP 
    BY day 
 ORDER 
    BY day ASC

does not work for me, any help

question from:https://stackoverflow.com/questions/65853085/how-to-use-multiple-columns

Answer 1

You can use GROUP_CONCAT for that purpose

CREATE TABLE name_table 
    (`id` int, `day` int, `line` int, `color` varchar(6))
;
    
INSERT INTO name_table 
    (`id`, `day`, `line`, `color`)
VALUES
    (1, 1, 1, 'white'),
    (2, 1, 2, 'orange'),
    (3, 2, 1, 'White'),
    (4, 2, 2, 'Orange'),
    (5, 2, 3, 'White')
;

SELECT day
     , GROUP_CONCAT(line ORDER BY  line ASC) kine
     , GROUP_CONCAT(color ORDER BY  line ASC) color  
  FROM name_table 
  GROUP 
    BY day 
 ORDER 
    BY day ASC

day | kine  | color             
--: | :---- | :-----------------
  1 | 1,2   | white,orange      
  2 | 1,2,3 | White,Orange,White

db<>fiddle here