What is the opposite of GROUP_CONCAT in MySQL?

by

You could use a query like this:

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
  colors
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY
  id,
  n.digit

Please see fiddle here. Please notice that this query will support up to 4 colors for every row, you should update your subquery to return more than 4 numbers (or you should use a table that contains 10 or 100 numbers).

More Related Contents:

  1. mysql pivot query results with GROUP BY
  2. How can I return pivot table output in MySQL?
  3. Can I concatenate multiple MySQL rows into one field?
  4. SQL split values to multiple rows
  5. MySQL pivot table query with dynamic columns
  6. How can I output MySQL query results in CSV format?
  7. Import data in MySQL from a CSV file using LOAD DATA INFILE
  8. MySQL and GROUP_CONCAT() maximum length
  9. How to use GROUP_CONCAT in a CONCAT in MySQL
  10. MySQL load NULL values from CSV data
  11. MySQL DISTINCT on a GROUP_CONCAT()
  12. Mysql query to dynamically convert rows to columns
  13. GROUP_CONCAT with limit
  14. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  15. Mysql Convert Column to row (Pivot table )
  16. Mysql query to dynamically convert rows to columns on the basis of two columns
  17. How to transpose mysql table rows into columns
  18. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  19. Setting column values as column names in the SQL query result
  20. Importing a CSV to MySQL with different date format
  21. How to export data from SQL Server 2005 to MySQL [closed]
  22. How can I correct MySQL Load Error
  23. MySQL Pivot Table Column Data as Rows
  24. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  25. How to join two tables using a comma-separated-list in the join field
  26. Import CSV to Update only one column in table
  27. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  28. Import CSV to Update rows in table
  29. is there a length limit to group_concat or another reason why it would not work on a text field
  30. How to import a csv file into MySQL workbench?

Leave a Comment