1

I have a table which has the column containing the comma separated list like 

ID :  List 
1  :  1,2,44,5      --row# 1
2  :  4,3,5,2,56,66 --row# 2

and so on. I want to write a select query which would have at max 10 columns Item1, Item2, Item3 .... Item10 and each column has a number from the corresponding comma separated list.

For example: for ID = 1

Item1 = 1, Item2 = 2, Item3 = 44, Item4 = 55 and all other columns would be null or empty

How can I write this in SQL?

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
Awais Amir
  • 165
  • 10
  • 1
    It can be done if you abuse SUBSTRING_INDEX function. – Mihai Aug 20 '14 at 19:00
  • Two parts to this question...first is parsing out the values, second is pivoting them into the display you want. Not the easiest question, but answers should already be on stack overflow. And as always need to include the 'why would anyone being saving data to a database in this array manner? problem is easiest to address by not inserting arrays into a single field in your database.' – Twelfth Aug 20 '14 at 19:03
  • @Twelfth actually database is already there and It is the requirement of project to write such a query. – Awais Amir Aug 20 '14 at 19:13
  • how many comma separated items can you have? – John Ruddell Aug 20 '14 at 19:18
  • @JohnRuddell 10 Items, if there are more than 10 items then just get the first 10 items and place them in the required columns of Select Query – Awais Amir Aug 20 '14 at 19:21

2 Answers2

4

You can do it like this:

select
    substring_index(substring_index(str,',',1),',',-1)AS c1
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 1 THEN substring_index(substring_index(str,',',2),',',-1) ELSE NULL END AS c2
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 2 THEN substring_index(substring_index(str,',',3),',',-1) ELSE NULL END AS c3
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 3 THEN substring_index(substring_index(str,',',4),',',-1) ELSE NULL END AS c4
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 4 THEN substring_index(substring_index(str,',',5),',',-1) ELSE NULL END AS c5
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 5 THEN substring_index(substring_index(str,',',6),',',-1) ELSE NULL END AS c6
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 6 THEN substring_index(substring_index(str,',',7),',',-1) ELSE NULL END AS c7
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 7 THEN substring_index(substring_index(str,',',8),',',-1) ELSE NULL END AS c8
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 8 THEN substring_index(substring_index(str,',',9),',',-1) ELSE NULL END AS c9
,   CASE WHEN LENGTH(str)-LENGTH(REPLACE(str,',','')) >= 9 THEN substring_index(substring_index(str,',',10),',',-1) ELSE NULL END AS c10
from test

Demo.

The expressions have two common parts:

  • LENGTH(str)-LENGTH(REPLACE(str,',','')) >= K - this subexpression determines if the string has at least K delimiters
  • substring_index(substring_index(str,',',K),',',-1) - this subexpression cuts out the element after the K-th delimiter
Community
  • 1
  • 1
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
1

This can be done by creating a user-defined function reference from MySQL Split String Function ,pass your column which contains the comma separated string as first parameter to this function,in second parameter pass the separator in your case comma is separator and in third parameter pass your desired position to get the string

SPLIT_STR(string, separator, position)

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And then you can query your data a below

SELECT 
SPLIT_STR(List, ',', 1) item1,
SPLIT_STR(List, ',', 2) item2,
SPLIT_STR(List, ',', 3) item3,
SPLIT_STR(List, ',', 4) item4,
SPLIT_STR(List, ',', 5) item5,
SPLIT_STR(List, ',', 6) item6,
SPLIT_STR(List, ',', 7) item7,
SPLIT_STR(List, ',', 8) item8,
SPLIT_STR(List, ',', 9) item9,
SPLIT_STR(List, ',', 10) item10
FROM t

Fiddle Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • http://stackoverflow.com/questions/2182668/how-to-split-comma-separated-text-in-mysql-stored-procedure... answer is already there as well.. – John Ruddell Aug 20 '14 at 19:33