MySQL Combine 5 Columns Into One (Alphabetized)-CodePudding

I have a table table1 like:

name1  name2  name3  name4  name5

Bob    Dave   Chris  Dan    Paula 
Dan    Bill   Sandy  Andy   Drew
Suzy   Bob    Bill   Dave   Chris

I need to concat name1, name2, name3, name4 and name 5 so that the names are in one column dash separated in alphabetical order.

Expected result:

op_team

Bob - Chris - Dan - Dave - Paula
Andy - Bill - Dan - Drew - Sandy
Bill - Bob - Chris - Dave - Suzy

I was trying to do a mix of LEAST and GREATEST with a CONCAT, but it got ugly fast. There has to be a better way!

Edit: I'm using MySQL 8.0.23

CodePudding user response：

I might consider something like the following.

I don't think there is an unpivot operator in MySQL (even in 8), hence all the UNION ALLs to do the unpivot. The inner query orders by name1,name2,name3,name4,name5 since you don't mention having an actual unique id per row that defines the order of the rows (the important thing here is that all the parts of the UNION ALL have the same order for row_number so that when it is grouped back together, the correct values are together).

SELECT group_concat(name1 ORDER BY name1 SEPARATOR ' - ')
FROM
(
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name1 FROM table1
 UNION ALL
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name2 FROM table1
 UNION ALL
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name3 FROM table1
 UNION ALL
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name4 FROM table1
 UNION ALL
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name5 FROM table1
) unpiv
GROUP BY rn

Alternatively you could do something like the following. This is the same thing, but it uses a CTE to avoid duplicating some of the text. Again, if you have an actual unique id for each row, you don't need to worry about the row_number bit... this is just for keeping track of which values belong together after the unpivot.

WITH sp AS
(
 SELECT row_number() over(ORDER BY name1, name2, name3, name4, name5) as rn, name1, name2, name3, name4, name5 FROM table1
)
SELECT group_concat(name1 ORDER BY name1 SEPARATOR ' - ')
FROM
(
 SELECT rn, name1 FROM sp
 UNION ALL
 SELECT rn, name2 FROM sp
 UNION ALL
 SELECT rn, name3 FROM sp
 UNION ALL
 SELECT rn, name4 FROM sp
 UNION ALL
 SELECT rn, name5 FROM sp
) unpiv
GROUP BY rn

To be clear, if you already have an actual unique identifier (let's call it uniqueid) on your table, you could just do:

   SELECT group_concat(name1 ORDER BY name1 SEPARATOR ' - ')
    FROM
    (
     SELECT uniqueid, name1 FROM sp
     UNION ALL
     SELECT uniqueid, name2 FROM sp
     UNION ALL
     SELECT uniqueid, name3 FROM sp
     UNION ALL
     SELECT uniqueid, name4 FROM sp
     UNION ALL
     SELECT uniqueid, name5 FROM sp
    ) unpiv
    GROUP BY uniqueid