Mysql – Split comma seperated values and concatenate with other row values in MYSQL

MySQLquery

I have a table like below

|   ID     |    Numbers       |  
-----------------------------------------------------------
|   6      |  3245,78765,1234,43566  |  
|   7      |  8245,88093,8326,92365  |

I want a query that can produce the below

|     Results         |
------------------------- 
|   3245_sub_6       | 
|   78765_sub_6       |
|   1234_sub_6       |
|   43566_sub_6      |
|   8245_sub_7       |
|   8326_sub_7      |
|   92365_sub_7     |

I am trying to split values in the above table into something like below;
like concat(splited value,'','sub','',id) something like that.

Can't seem to get how to write this query. Been beating myself up for days. Please Help

Best Answer

SELECT DISTINCT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers,',',num),',',-1),'_sub_',id) results
FROM `table`, (SELECT 1 num UNION ALL
               SELECT 2     UNION ALL
               SELECT 3     UNION ALL
               SELECT 4) nums

The numbers count in nums subquery is to be equal or greater than max values count in CSV field. If values count is fixed and numbers count is equal to it, you may remove DISTINCT.

If MySQL version is 8+, the numbers pseudotable can be generated in CTE.

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

MySQL Join – How to SELECT and JOIN with Not Exists Values

Change AND c.tar_id = 87 to WHERE c.tar_id = 87.

Your soultion would work only if it was an INNER JOIN instead. With LEFT JOIN it works different. The filter is applied for the join predicate and the result is different.

Best Answer

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

MySQL Join – How to SELECT and JOIN with Not Exists Values

Related Question