0

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'

thirtywest
  • 13
  • 5
  • You are using two tags from different databases? Which is it? Mysql or SQL Server. Also please tell us which version of your DB it is (if mysql it makes a huge difference as to what one can use) – Jorge Campos Nov 21 '22 at 19:03
  • *the zip line happens to come before state* what does this mean, there is no "before" or "after" unless you specify an order, there is nothing in your data that indicates a sequence. – Stu Nov 21 '22 at 19:04
  • @Stu: "comes before" meant that in the structure of the table, the zip exists before the state entry. From what I read, mySQL (absent me manipulating things) will just output based on the order things are found in the structure. And for Jorge: it's mySQL--apologies. 8.0.27 – thirtywest Nov 21 '22 at 19:12

1 Answers1

1

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.

ysth
  • 96,171
  • 6
  • 121
  • 214
  • Yes. That worked perfectly. THANKYOU for explaining that. I had been dancing around so many permutations. – thirtywest Nov 21 '22 at 19:16
  • I tried doing a concat on the conditional aggregation, but I couldn't solve the syntax. – thirtywest Nov 21 '22 at 19:26
  • is the "citystzip" you wrote before the 'FROM themes' how the value is referenced? I'm outputting to an array and although phpmyadmin works great I cannot find the data when sent to an array – thirtywest Nov 21 '22 at 22:58
  • Yes, you can place an identifier after an expression (optionally after ` AS `) that names the column in the results. – ysth Nov 21 '22 at 23:49