SQLite convert single row to multiple rows

Question

SQLite
I want to convert single row value seperate by ',' to multiple rows

Example :

Single_Row

6,7,8,9,10,11,12,13,14,15,16

Result must be :

MultipleRows

I tried doing it with substr function but getting unexpected result

select 
 numbers.n,
substr(CbahiHSSpecialtyUnits.units,numbers.n,1) 
from
  numbers inner join CbahiHSSpecialtyUnits
  on  LENGTH(CbahiHSSpecialtyUnits.units)
     - LENGTH(REPLACE(CbahiHSSpecialtyUnits.units, ',', ''))>=numbers.n-1
  WHERE HsSubStandardID=22 and SpecialtyID=2 and  numbers.n>0
  order by numbers.n;

One good thing is I'm getting number of rows correct.. But the values that should be separated is wrong .. Please note numbers table is I have created for indexing purpose, with the help of this post. SQL split values to multiple rows

Looking at what you are trying to do, which seems a very good idea, it seems that you can contribute a valuable insight into database design to other users. I recommend to create a self-answered question like "Why change from CSV in columns to one-to-many relation design?". Assuming of course that is what you do/did. (No sarcasm intended. If you let me know you are almost guaranteed an upvote there.) You could even link here as "How I did it with the help of SQLite...". — Yunnosch, Jan 11 '21 at 13:32

forpas · Accepted Answer · 2021-01-11T14:08:14.590

2

You can do it with a recursive CTE:

WITH cte AS (
  SELECT SUBSTR(Units, 1, INSTR(Units || ',', ',') - 1) col,
         SUBSTR(Units, INSTR(Units || ',', ',') + 1) value
  FROM CbahiHSSpecialtyUnits
  WHERE HsSubStandardID=22 AND SpecialtyID = 2
  UNION ALL
  SELECT SUBSTR(value, 1, INSTR(value || ',', ',') - 1),
         SUBSTR(value, INSTR(value || ',', ',') + 1)
  FROM cte
  WHERE LENGTH(value) > 0
)
SELECT col 
FROM cte
WHERE col + 0 > 0

Or, if you know the upper limit of the numbers is, say 20 and there are no duplicates among the numbers:

WITH cte AS (SELECT 1 col UNION ALL SELECT col + 1 FROM cte WHERE col < 20)
SELECT c.col 
FROM cte c INNER JOIN CbahiHSSpecialtyUnits u
ON ',' || u.Units || ',' LIKE '%,' || c.col || ',%'
WHERE HsSubStandardID=22 AND SpecialtyID = 2

See the demo.
Results:

col
6
7
8
9
10
11
12
13
14
15
16

edited Jan 11 '21 at 14:08

answered Jan 11 '21 at 13:12

forpas

160,666
10
38
76

@Yunnosch yes, I'm not used yet to the new table formatting. – forpas Jan 11 '21 at 13:38
1

Same with me. Actually, seeing the table now, I am not so sure about "pretty" anymore.... Not your fault. – Yunnosch Jan 11 '21 at 13:38
I'll mark your solution as answered. However I have found the solution already. Mr. Yunnosh. But your solution is quite understandable. Thank you so much and for your time. – obaid Jan 12 '21 at 05:51

score 0 · Answer 2 · answered Jan 12 '21 at 05:52

I got the solution

http://www.samuelbosch.com/2018/02/split-into-rows-sqlite.html

WITH RECURSIVE split(predictorset_id, predictor_name, rest) AS (
  SELECT CbahiHSSpecialtyUnits.SpclUnitSerial, '', units || ',' FROM CbahiHSSpecialtyUnits WHERE HsSubStandardID=22 and SpecialtyID=2 
   UNION ALL
  SELECT predictorset_id, 
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
   WHERE rest <> ''

SQLite convert single row to multiple rows

Example :

Single_Row

Result must be :

MultipleRows

2 Answers2