1

I have 2 columns

ID          value
---      ----------
1        "1,abc,,3"
2        "2,qwe,the,4"

How can I convert the delimited value to a table

ie:

col1         col2        col3       col4
-----        -----       -----      ----
1            abc                      3
2            qwe         the          4

Hope to get some help real soon. Thanks in advance..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeswin James
  • 13
  • 1
  • 5
  • there will be an upgrade on the dbserver from 2005 to 2008.. so the method used should be compatible. atleast for now it should work with 2005 – Jeswin James Mar 11 '13 at 10:06
  • 1
    @marc_s Fair enough I think you are right here. – lc. Mar 11 '13 at 10:09
  • Do you simply want to import the text file containing these comma-delimited strings? Is this something that happens often, so that exporting the column and then reimporting it would be undesirable? – Tim Mar 11 '13 at 12:40
  • the csv was not in text file.. its a column inside the table varchar – Jeswin James Mar 13 '13 at 10:57

1 Answers1

1

This is just a sample for 4 columns as per your question. This can be generalized :

with cte as(
SELECT id,CHARINDEX(',', value) First_occurence,

CHARINDEX(',', value, CHARINDEX(',', value)+1) Second_occurrence,

CHARINDEX(',', value, CHARINDEX(',', value, CHARINDEX(',', value)+1)+1) Third_occurrence

from test)

select  substring(value,1,first_occurence-1) col1,
substring(value,first_occurence+1,Second_occurrence-(first_occurence+1)) col2,
substring(value,Second_occurrence+1,Third_occurrence-(Second_occurrence+1)) col3,
substring(value,Third_occurrence+1,len(value)) col4

from test a
inner join 
cte b
on a.id=b.id

Generalised :

Declare @col_count  int=1

;with cte as(
SELECT id, @col_count col,value,CHARINDEX(',', value) occurence,substring(value,1,CHARINDEX(',', value)-1) col_val from test

  union all
select id, col+1 col,value, CHARINDEX(',', value, occurence+1),
  substring(value,occurence+1,CHARINDEX(',', value, occurence+1)-(occurence+1))

  from cte
 where  (occurence+1)< len(value)
union all

select id, col+1 col,value, occurence+1,
  substring(value,occurence+1,len(value))
  from cte
 where  (occurence+1)= len(value)
)



SELECT [1], [2], [3], [4]
from
(
  SELECT col, col_val,
    row_number() over(partition by col order by col_val) rn
  from cte
) as st
pivot
(
  max(col_val)
  FOR col in ([1], [2], [3], [4])
) as pivottable

Just add [5] ,[6],.... for more column in final select.

SQL Fiddle

Ravi Singh
  • 2,042
  • 13
  • 29