How do you order the results of mySQL group_concat() when all the values come from one column?-CodePudding

I have a simple table that has 3 columns: id, variable, and value. There are a lot of user IDs and a lot of different variables per person too (all the specs from their profiles to be exact), but I only need to query one person at a time with these three things.

id	variable	value
1	city	chicago
1	zip	60000
1	state	IL

I'm using a query in an attempt to produce: "chicagoIL60000", but because of how the table was made (forum software) the zip line happens to come before state, so the results is actually out of order: "chicago60000IL".

This is the query I have:

SELECT GROUP_CONCAT(`value` SEPARATOR "") FROM themes WHERE `id` = '1' AND `variable` IN ('city', 'state', 'zip')

I am unclear how to either ORDER or GROUP this because all the values are in a single column, and all the examples I find deal with ordering or grouping by a specific column.

I've tried changing the order of the variable array in the query, but mySQL doesn't care about that.

I have also tried the following query that, while giving the proper order, doesn't actually concatenate the results to what I want; rather, it just makes three fields.

select 
   group_concat(case when `variable` = 'city' then `value` end) as city,
   group_concat(case when `variable` = 'state' then `value` end) as state,
   group_concat(case when `variable` = 'zip' then `value` end) as zip

from themes where id = '1'

CodePudding user response：

In MySQL, you can specify an ORDER BY clause in GROUP_CONCAT, and use a CASE expression to get the order you want:

SELECT GROUP_CONCAT(
  `value`
  order by case variable when 'city' then 1 when 'state' then 2 else 3 end
  SEPARATOR ""
) citystzip
FROM themes
WHERE `id` = '1' AND `variable` IN ('city', 'state', 'zip')

fiddle

Alternatively, take your attempt using conditional aggregation and just concat the three:

select concat(
    group_concat(case when `variable` = 'city' then `value` end),
    group_concat(case when `variable` = 'state' then `value` end),
    group_concat(case when `variable` = 'zip' then `value` end)
) citystzip
from themes
where `id` = '1'

Though I would use MAX instead of GROUP_CONCAT, which has the advantage of only showing one city, state, or zip if there are multiple rows with the same id/variable.