0

For example, there are three rooms.

1|gold_room|1,2,3

2|silver_room|1,2,3

3|brown_room|2,4,6

4|brown_room|3

5|gold_room|4,5,6

Then, I'd like to get

gold_room|1,2,3,4,5,6

brown_room|2,3,4,6

silver_room|1,2,3

How can I achieve this?

I've tried: select * from room group by name; And it only prints the first row. And I know CONCAT() can combine two string values.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
c-an
  • 3,543
  • 5
  • 35
  • 82
  • 5
    You probably know this, but just in case, I'll say that this is a flawed data model. Storing multiple values in one column is just begging for trouble, and this is just the beginning of it. – Eric Brandt Jun 01 '20 at 15:59
  • 1
    That said, for `brown_room`, that's not a concatenation. You've unpivoted the values, sorted them, and repivoted them. Is that a requirement? – Eric Brandt Jun 01 '20 at 16:02
  • Ah.. I think it's not concatenation. And Yes, I want sorted array @EricBrandt – c-an Jun 01 '20 at 16:07
  • 1
    I edited the title of this question to describe the goal more clearly. Instead of "concatenate," I would say "merge." – Bill Karwin Jun 01 '20 at 16:09
  • 5
    Fix your data model! Don't store multiple values in a string! Don't store numbers as strings! And "two rooms"??? – Gordon Linoff Jun 01 '20 at 16:12
  • @GordonLinoff I agree.. but I thought it would be faster to implement. The data I have is arrays in a string like the question. And what I need is the other numbers. For example, if the data is '1,3,7'. Then, I need '2,4,5,8,9'. And additionally, there's one more parameter that is night value(the numbers show the order of classes). The night value also contains arrays in a string format like '1' or '3,4'. However, this is different time from the daytime schedule. So, it'd be better to store like '10' or '12,13'. And I have no idea how to parse the data to one rows. Do you have any suggestions? – c-an Jun 01 '20 at 16:20
  • 2
    Whenever you find yourself putting comma-separated data into a single column in any RDBMS, you're doing something **very wrong**. – Joel Coehoorn Jun 01 '20 at 16:20
  • 2
    You will regret using this database design. If you have any hope of a robust solution, fix it now. – Honeyboy Wilson Jun 01 '20 at 16:20
  • 2
    The road to hell is paved with code that was "faster to implement". ;) The extra time you spend correcting the data model now will be a fraction of the time you'll waste trying to work with this model down the road. – Eric Brandt Jun 01 '20 at 18:43
  • @JoelCoehoorn I agree with you. But 1. the data is more than 2000 rows. 2. I have just one day. 3. this is not production level 4. I need to parse the data with my hands. I think I don't have any choice. And I wish I could make them as you suggest, If there's simple way. I think it can take more than a week if I do it with my hands. – c-an Jun 02 '20 at 00:00
  • I created new question related to this here: https://stackoverflow.com/questions/62143110/how-can-i-parse-delimited-list-in-string-to-rows – c-an Jun 02 '20 at 00:44

2 Answers2

2

Please use below query,

select col2, GROUP_CONCAT(col3) from data group by col2;

Below is the Test case,

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ab35e8d66ffe3ac6436c17faf97ee9af

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
0

I'm not making an assumption that the lists don't have elements in common on separate rows.

First create a table of integers.

mysql> create table n (n int primary key);
mysql> insert into n values (1),(2),(3),(4),(5),(6);

You can join this to your rooms table using the FIND_IN_SET() function. Note that this cannot be optimized. It will execute N full table scans. But it does create an interim set of rows.

mysql> select * from n inner join rooms on find_in_set(n.n, rooms.csv) order by rooms.room, n.n;
+---+----+-------------+-------+
| n | id | room        | csv   |
+---+----+-------------+-------+
| 2 |  3 | brown_room  | 2,4,6 |
| 3 |  4 | brown_room  | 3     |
| 4 |  3 | brown_room  | 2,4,6 |
| 6 |  3 | brown_room  | 2,4,6 |
| 1 |  1 | gold_room   | 1,2,3 |
| 2 |  1 | gold_room   | 1,2,3 |
| 3 |  1 | gold_room   | 1,2,3 |
| 4 |  5 | gold_room   | 4,5,6 |
| 5 |  5 | gold_room   | 4,5,6 |
| 6 |  5 | gold_room   | 4,5,6 |
| 1 |  2 | silver_room | 1,2,3 |
| 2 |  2 | silver_room | 1,2,3 |
| 3 |  2 | silver_room | 1,2,3 |
+---+----+-------------+-------+

Use GROUP BY to reduce these rows to one row per room. Use GROUP_CONCAT() to put the integers together into a comma-separated list.

mysql> select room, group_concat(distinct n.n order by n.n) as csv 
  from n inner join rooms on find_in_set(n.n, rooms.csv) group by rooms.room
+-------------+-------------+
| room        | csv         |
+-------------+-------------+
| brown_room  | 2,3,4,6     |
| gold_room   | 1,2,3,4,5,6 |
| silver_room | 1,2,3       |
+-------------+-------------+

I think this is a lot of work, and impossible to optimize. I don't recommend it.

The problem is that you are storing comma-separated lists of numbers, and then you want to query it as if the elements in the list are discrete values. This is a problem for SQL.

It would be much better if you did not store your numbers in a comma-separated list. Store multiple rows per room, with one number per row. You can run a wider variety of queries if you do this, and it will be more flexible.

For example, the query you asked about, to produce a result with numbers in a comma-separated list is more simple, and you don't need the extra n table:

select room, group_concat(n order by n) as csv from rooms group by room

See also my answer to Is storing a delimited list in a database column really that bad?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828