Query Multiple Rows into a single row

Question

Last day I came across a requirement where I had do share all the configs set for an entity to non-Engineering team - means, I need an Excel/CSV.

My table structure looks like

id | entity_id | config_id | value | is_active

And my resultant csv should look something like:

Entity_id, config1_value, config2_value, config3_value ...

I did get the output, but not with good ways. I am not convinced with the approach I have taken up. My query was like

SELECT entity_id,
group_concat(if(config_id = 1, value, "")) as config1_value,
group_concat(if(config_id = 2, value, "")) as config2_value,
group_concat(if(config_id = 3, value, "")) as config3_value,
...
FROM table_name
WHERE is_active = 1
AND config_id in (1,2,3...)
group by entity_id;

The query worked fine and as the table was still having less than 100K records, it was fast as well. As you see, if I need to add or delete new config key to report, I had to make changes to 2 line at the least. I am running MySQL 5.1 . What could be a better solution?

CONCAT_WS is to join multiple columns, right? Here each entity_id/config_id is in different row — georgecj11, Feb 11 '14 at 17:41

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Here you go: http://sqlfiddle.com/#!2/b266c2/17

Not too much different than what you did. Sounds like you are looking for a pivot table or crosstab report.

Also see this answer: https://stackoverflow.com/a/8922865/3112803

But if you want to do it dynamically so you do not have to keep adding/removing these columns max(if(config_id = 1, value, "")) as config1_value, then have a look at this article 'Automate pivot table queries' - http://www.artfulsoftware.com/infotree/qrytip.php?id=523

edited May 23 '17 at 12:05

Community

1
1

answered Feb 11 '14 at 18:01

gfrobenius

3,987
8
34
66

did mean to say is **not**...? Checkout that article. It could definitely save you some typing as your number of different values start to increase. – gfrobenius Feb 11 '14 at 18:12
Ya, I like the approach, would definitely give a try. I was doing now with CONCATENATE function in MS-Excel, doesnot make much time either for me nowadays :) – georgecj11 Feb 11 '14 at 18:22

Gordon Linoff · Answer 2 · 2014-02-11T18:03:08.337

0

I suspect that the problem is missing config values for some entities. One way is to create a row for each config value for each entity:

SELECT e.entity_id,
       group_concat(coalesce(t.value, '') order by c.config_id) as values
FROM (select distinct entity_id where is_active = 1 from table_name) e cross join
     (select 1 as config_id union all select 2 union all select 3
     ) c left outer join
     table_name t
     on t.entity_id = e.entity_id and t.config_id = t.config_id
WHERE t.is_active = 1
group by entity_id;

An alternative approach is the path you were on, where each value goes in a separate column. For this, use max() instead of group_concat():

SELECT entity_id,
       max(if(config_id = 1, value, NULL)) as config1_value,
       max(if(config_id = 2, value, NULL)) as config2_value,
       max(if(config_id = 3, value, NULL)) as config3_value,
...
FROM table_name
WHERE is_active = 1
AND config_id in (1,2,3...)
group by entity_id;

edited Feb 11 '14 at 18:03

answered Feb 11 '14 at 17:50

Gordon Linoff

1,242,037
58
646
786

In fact, if some config was not set, it will ""(empty string) which was acceptable for me. The performance of query 1 suspicious - its using cross join/union/derive table/group by, bad combination And using MAX, there need to be a small change max(if(config_id = x, value, null)) as configx_value [so my value can have negative integers too]. – georgecj11 Feb 11 '14 at 18:00
@cjg . . . When I looked at the problem, I thought you had to do it the first way, because I was thinking you wanted all the values in one list. Then I realized having the values in separate columns is fine, so the second is surely the better way to go in that situation. – Gordon Linoff Feb 11 '14 at 18:02

Query Multiple Rows into a single row

2 Answers2