1

I have a really peculiar task of creating a SELECT with following conditions:

Source table is as follows:

+----------------+
| source         |
+-----+----------+
| id  | values   |
+-----+----------+
| 1   | 1,2,3    |
+-----+----------+
| 3   | 4,5,6    |
+-----+----------+
| 8   | 1,4,7    |
+-----+----------+
| 9   | 8,9      |
+-----+----------+
| 21  | 10,11,12 |
+-----+----------+
| 69  | 11,45,89 |
+-----+----------+

And the result after some kind of SELECT would be

+--------------------------+
| target                   |
+-------+------------------+
| sid   | values           |
+-------+------------------+
| 1,3,8 | 1,2,3,4,5,6,7    |
+-------+------------------+
| 9     | 8,9              |
+-------+------------------+
| 21,69 | 10,11,12,45,89   |
+-------+------------------+

Meaning it concatenates all the ids and values, where any of the set values have an element in common.

Does anybody have any idea how to do it? Be it in MySQL, or SQL Server. Ideally without functions/stored procedures.

TT.
  • 15,774
  • 6
  • 47
  • 88
peterC_
  • 49
  • 7
  • I just want to clarify that I am reading the table properly. `id` is a primary key and `values` is a `varchar` containing all of the values comma separated? – duncan Nov 14 '16 at 16:28
  • Care to explain the logic bit more. Why is 1st and 2nd row concatenated and why is 3rd and 4th row are not concatenated – Pரதீப் Nov 14 '16 at 16:34
  • Also not only user defined functions/stored procedures differs between Mysql and MSSQL. There are lot more inbuilt functions differs between both so choose one. – Pரதீப் Nov 14 '16 at 16:36
  • If you tag with `sql` you should be asking for ANSI-SQL solutions. If you want for specific RDBMS's, tag appropriately for those. In any case, [here](http://stackoverflow.com/a/35172160/243373)'s how it can be done in SQL Server. – TT. Nov 14 '16 at 17:04
  • @duncan actually id is not necessarily a PK, as the my source table will eventually be created by a subselect – peterC_ Nov 15 '16 at 07:58
  • @Prdp: if you would "explode" the values in the source table and the set will contain a value which is also in the other id, the values will merge into one distinct set and the source ids will get concatenated – peterC_ Nov 15 '16 at 07:59
  • @TT. thanks, I'll tag it now – peterC_ Nov 15 '16 at 08:00
  • @TT. I checked also your proposed solution, but I'm not sure this has anything to do with XML. – peterC_ Nov 15 '16 at 08:05
  • @peterC_ It doesn't, but `GROUP_CONCAT` as you call it in SQL Server is typically done with a `FOR XML PATH('')` hack due to the fact that SQL Server doesn't have that aggregate function (yet, I hope this changes in the future). – TT. Nov 15 '16 at 08:10
  • *"Ideally without functions/stored procedures."* >> At the very least you will have to split the strings to get individual values. In SQL Server 2016, you have the `STRING_SPLIT` function, in previous versions you will have to find one on SO to split strings. Also, why are the values stored that way? That's not how they should be stored in a relational database. You could argue that a query or stored procedure shouldn't produce the rows in the expected output that way either, but should return the results in a normalized way (ie, group sid's, assign super-id, have super-id connected to values) – TT. Nov 16 '16 at 06:42
  • @TT. the source table is already a result of a query, which concats a with group by. Which is simple, but now I need to basically merge sets with "overlapping" values and perserve all the keys. I can of course do it programatically in PHP for example, but I wanted to keep things on the SQL server, where they belong – peterC_ Nov 16 '16 at 07:21

0 Answers0