How to Split Comma-Separated Values in SQL into Multiple Rows?

Transforming Comma-Separated SQL Values into Multiple Rows

A frequent SQL task involves splitting a single cell's contents into multiple rows. For example, consider this table:

<code class="language-sql">id | name
1  | a,b,c
2  | b</code>

The goal is to restructure this data like so:

<code class="language-sql">id | name
1  | a
1  | b
1  | c
2  | b</code>

Method 1: Leveraging a Numbers Table

One effective solution utilizes a pre-existing "numbers" table (a table containing a sequential list of integers). The query below demonstrates this approach:

<code class="language-sql">SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) AS name
FROM
  numbers
INNER JOIN
  tablename
ON
  CHAR_LENGTH(tablename.name) - CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
ORDER BY
  id, n</code>

Method 2: Dynamically Generating Numbers (No Numbers Table Required)

If a dedicated numbers table isn't available, a temporary number sequence can be generated within the query itself:

<code class="language-sql">SELECT
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) AS name
FROM
  (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS numbers
INNER JOIN
  tablename
ON
  CHAR_LENGTH(tablename.name) - CHAR_LENGTH(REPLACE(tablename.name, ',', '')) >= numbers.n - 1
ORDER BY
  id, n</code>

Remember to adjust the UNION ALL section in the second method to accommodate the maximum number of comma-separated values you anticipate in your name column. Both methods achieve the same outcome—splitting the comma-separated values into individual rows.

The above is the detailed content of How to Split Comma-Separated Values in SQL into Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!