CODEWITHSUNDEEP
mysqlgroup-bygroup-concat
dishanm
dishanm

Reputation: 120

Mysql - How do I avoid group by but still with concat and group concat I would need to combine multiple columns and rows results

I have something like in table

mysql> select uuid , short-uuid FROM sampleUUID WHERE identifier ="test123";
+--------------------------------------+-------------+
|       uuid                           | short-uuid   |
+--------------------------------------+-------------+
| 11d52ebd-1404-115d-903e-8033863ee848 | 8033863ee848 |
| 22b6f783-aeaf-1195-97ef-a6d8c47261b1 | 8033863ee848 |
| 33c51085-ccd8-1119-ac37-332510a16e1b | 332510a16e1b |
+--------------------------------------+-------------+

I would be needing a result like (grouped all in single row, single value w.r.t uuid and short-uuid being same)

| uuidDetails                                                         
+----------------------------------------------------------------------------------------------------------------+-------------+
| 11d52ebd-1404-115d-903e-8033863ee848,22b6f783-aeaf-1195-97ef-a6d8c47261b1|8033863ee848&&33c51085-ccd8-1119-ac37-332510a16e1b| 332510a16e1b |
+----------------------------------------------------------------------------------------------------------------+-------------+

(basically grouping uuid and short uuid in a single row from multiple rows and columns)

I know this can be achieved by select GROUP_CONCAT(uuid)FROM sampleUUID WHERE identifier ="test123" group by short-uuid; but i don't wanna use group by here because that give multiple rows, i would need all in one row .

I have tried with below stuffs but failed to get the the results in single row

select ANY_VALUE(CONCAT_WS( '||',CONCAT_WS('|',GROUP_CONCAT(uuid) SEPARATOR ','),short-uuid)) )as uuidDetails from sampleUUID 
where identifier ="test123";

this resulted like below with not appending short-uuid properly (there is only 1 short uuid appended here,Actually it needs to be grouped first 2 uuids with 1 short(because same short-uuid) uuid and 3rd uuid with other short uuid)

| uuidDetails                                                         
+----------------------------------------------------------------------------------------------------------------+-------------+
| 11d52ebd-1404-115d-903e-8033863ee848,22b6f783-aeaf-1195-97ef-a6d8c47261b1,33c51085-ccd8-1119-ac37-332510a16e1b| 332510a16e1b |
+----------------------------------------------------------------------------------------------------------------+-------------+

which is not i expected

Any help here will be appreciated . Thank you

Upvotes: 0

Views: 713

Answers (1)

Barmar
Barmar

Reputation: 781028

Use nested queries. 

SELECT GROUP_CONCAT(result ORDER BY result SEPARATOR '&&') AS uuidDetails
FROM (
    SELECT CONCAT(GROUP_CONCAT(uuid ORDER BY uuid SEPARATOR ','), '|', short_uid) AS result
    FROM sampleUUID
    WHERE identifier = 'test123'
    GROUP BY short_uid
) AS x

NOTE: If there is no requirement for ordering of the UUID values, we can use ORDER BY inside the GROUP_CONCAT aggregates to make the result more deterministic, so the query will return just one of a number of possible results given the same data e.g. return aa,bb|1&&cc|3 rather than bb,aa|1&&cc|3 or cc|3&&aa,bb|1 or cc|3&&bb,aa|1.

Upvotes: 3

Related Questions