Turning comma seperated column into indiviual column

Question

I am working on enquiry page in which user has to enter the number of kids with their respective age according to user given number of textboxes appeared for e.g. if user enter number of kids as 3 then 3 textboxes appears for enter age and if user enter 4 kids then 4 textboxes appears now I have to insert their age in single field like 10,6,8,9 like this. Till this point I have no issue.

The problem is when I have to fetch the value from database and show the age of kids in different textboxes. I have a query which works fine if I entered the record 10,8,9 but a problem occurs if I enter more values than this.

I am writing my query here please do the need full changes in my query so that I can fetch more than 3 values. Thanks in advance.

select ID,
SUBSTRING(Value,1,charindex(',',Value)-1) as v1,
SUBSTRING (Value,(charindex(',',Value)+1),(len(Value)-charindex(',',reverse(Value))-CHARINDEX(',',Value))) as v2,
SUBSTRING (Value,(Len(Value)-charindex(',',reverse(Value))+2),len(Value)) as v3 
from Test where ID=2

score 1 · Answer 1 · answered Apr 23 '15 at 12:49

 SELECT ID, LTRIM(x.XmlCol.value('.','varchar(100)')) 'Item'
    FROM 
    (
        SELECT ID, CAST('<A>'+REPLACE(Value,',','</A><A>')+'</A>' AS XML) 'ValueList'
        FROM Test) Mytab
    CROSS APPLY Mytab.ValueList.nodes('/A') x(xmlcol)
WHERE ID = 2

score 0 · Answer 2 · edited May 23 '17 at 10:24

You need to split a string, check out these answers: 1, 2, 3, 4 etc.

Nevertheless it's usually a bad practice to dynamically expand a record on the server-side. Moreover that could be extremely slow on somewhat large selections. I'd recommend to revise your DB structure:

If possible and the maximum number of items couldn't exceed 4, add separate fields for every item. If the limit could change consider adding separate table for kids linked via FK to the main one.
If above is impossible consider client-side splitting.

Turning comma seperated column into indiviual column

2 Answers2