Sybase IQ - Splitting comma separated values in a column into rows

Question

I see other solutions on here regarding splitting data values into rows but so far none of them work on Sybase IQ - either because it's not supported or I don't have the proper access to create procedures. I can do this very easily in Java but trying to do it in a query instead so I can avoid the overhead.

So what I'm trying to do is take this...

 ID     | Data
 abc    | 18,20,22
 def    | 17,19
 ghi    | 13,19

And convert it to...

 ID     | Data
 abc    |  18
 abc    |  20
 abc    |  22
 def    |  17
 def    |  19
 ghi    |  13
 ghi    |  19

1. I tried the recursive method but got an error that the remote server does not support it

2. I tried the XML version below but I keep getting a syntax error on the last line. The only thing I can think of is that Cross Apply isn't supported since Aqua Data isn't highlighting the word as it does with everything else.

    SELECT A.ID,  
         Split.a.value('.', 'VARCHAR(100)') AS Data  
    FROM  
    (
    SELECT ID,  
        CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
    FROM  mytable
    ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 
   

3. I'm unable to create functions / procedures because of permissions

4. I tried using this Sybase function (sa-split-list)...

   http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html

   But not sure how to incorporate it

Anyone able to help?

Answer 1

I think it can be done with a recursive query, such as the following:

with  recursive temp (n, ID, init_str, next_comma, value, str) as (
    select 0, ID, data as init_str, cast(null as integer), cast(null as varchar(200)), init_str as str
    from (
        select 'abc' as ID, '18,20,22' as Data union
        select 'def' as ID, '17,19' as Data union
        select 'ghi' as ID, '5' as Data
    ) mydata

    union all

    select n+1, ID, init_str, locate(str, ',')
    , case when locate (str, ',') = 0 then str else substr(trim(str), 0, locate (str, ',')) end
    , case when locate(str, ',') = 0 then '' else substr(trim(str), locate(str, ',')+1) end
    from temp
    where len(trim(str)) > 0
)
select *
from temp
where value is not null
;

Unfortunately, I think Sybase IQ only has partial support for recursion. In particular, when I replace the mydata CTE with a reference to a real table, the whole thing collapses.

Answer 2

Try list function

CREATE TABLE #T1 (ID CHAR(3),DATA CHAR(50))

INSERT INTO  #T1
SELECT ID,LiST(LTRiM(RTRiM(Data))) FROM mytable GROUP BY ID

SELECT * FROM #T1

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1604/doc/html/jcu1290709667623.html