Select with Concat_ws all type from group of record-CodePudding

Using SQL, I can do something like:

SELECT T1.ID AS ORDER_ID, T2.CODE AS CODE  
FROM SCHEMA.TABLE1 AS T1 
LEFT JOIN SCHEMA.TABLE2 AS T2 ON T1.ID = T2.O_ID

Output:

ORDER_ID    CODE 
-----------------
25005CM73   AB 
25005CM73   CD 
25005CM73   EF 
25CMMM074   CD 
25CMMM074   AB 
25CMMM074   EF 
25CMMB075   GH 
25CMMB075   IG 
25CMMB075   KL

I would like to return only 1 row for each ORDER_ID with CONCAT.

Expected output:

ORDER_ID    CODE 
--------------------
25005CM73   AB,CD,EF
25CMMM074   CD,AB,EF
25CMMB075   GH,IJ,KL

I don't know how to write the code for a group of ORDER_ID.

CodePudding user response：

Try this using GROUP_CONCAT:

SELECT 
  ORDER_ID,
  GROUP_CONCAT(CODE ORDER BY CODE SEPARATOR ',') as CODE
FROM
 (
  SELECT T1.ID AS ORDER_ID, T2.CODE AS CODE  
  FROM SCHEMA.TABLE1 AS T1 
  LEFT JOIN SCHEMA.TABLE2 AS T2 ON T1.ID = T2.O_ID
 ) as a
GROUP BY ORDER_ID;

Note: You can directly use GROUP_CONCAT in the query while joining instead of using sub-query.