mishaluba (6/27/2010)

---

Hello,

I have a table with three columns containing CSV values. I need to normalize them. The order of the CSV values is meaningful (explained more below) Here is the sample DDL:

CREATE TABLE #t (id int, kpi1 varchar(16), kpi2 varchar(16), kpi3 varchar(16))

insert into #t (id, kpi1, kpi2, kpi3)

values(1, '0.1,0.11,0.111', '0.2,0.22,0.222', '0.3,0.33,0.333')

insert into #t (id, kpi1, kpi2, kpi3)

values(2, '0.4,0.44,0.444', '0.5,0.55,0.555', '0.6,0.66,0.666')

select *

from #t

My desired result would look something like this:

id kpi1 kpi2 kpi3 items1 items2 items3

----------- ---------------- ---------------- ---------------- ---------- -----------

1 0.1,0.11,0.111 0.2,0.22,0.222 0.3,0.33,0.333 0.1 0.20.3

1 0.1,0.11,0.111 0.2,0.22,0.222 0.3,0.33,0.333 0.11 0.220.33

1 0.1,0.11,0.111 0.2,0.22,0.222 0.3,0.33,0.333 0.111 0.2220.333

2 0.4,0.44,0.444 0.5,0.55,0.555 0.6,0.66,0.666 0.4 0.50.6

2 0.4,0.44,0.444 0.5,0.55,0.555 0.6,0.66,0.666 0.44 0.550.66

2 0.4,0.44,0.444 0.5,0.55,0.555 0.6,0.66,0.666 0.444 0.5550.666

I hope this is clear. Basically I want to split CSV values in each of the columns without multiplying the number of records every time I do the split, but just to append them to the same id. Once again, the order of CSV values is meaningful for the subsequent manipulations. For example: 0.1, 0.2, 0.3 (first values from kpi1, kpi2, kpi3) form a meaningful set of values, that's why I want them to be in the first row. The next row contains second values from each of the kpi's: 0.11, 0.22, 0.33. Since I do have an id in my source table and the function returns this id, I thought I could do a JOIN instead of CROSS APPLY, but this gives me a syntax error: