[madhudvm]

Can someone please help me out in writing the Sql to concatenate the Text_desc
for each code for all the seq_nos in the ascending order of seq_no and load into the target table. I want to implement this in Sybase 12.5.0.1.

Source table : S1 (2700 records in S1 table)

Code Seq_no Text_desc
---------------------------
1579 0 BCN5, CO20, ER75
1579 1 FP5, WC6, MATW, OPRH
1579 2 T
1600 0 TW, FP5, AS5, MHSA15, DME20
1600 1 ER100, UR40

Target table: T1

Text_desc (concatenate all the strings for each code in ascending order of seq_no)

code Text_desc
----- ---------
1579 BCN5, CO20, ER75FP5, WC6, MATW, OPRHT
1600 TW, FP5, AS5, MHSA15, DME20ER100, UR40

thanks,
maddy

[aflorin27]

You may use the CONVERT function

[mike_bike_kite]

It can be done in SQL but it’s a pain to write.

Why not just

• bcp the S1 data out
• read the bcp file using your favourite language (php, perl etc)
• process the file as stated
• output the new data
• bcp back into T1

Mike

[r937]

use the sybase list() function

mike, if you're gonna do it outside of sybase, make it easy

1. export the data to MySQL
2. use the GROUP_CONCAT() function
3. import

[madhudvm]

Thanks for your responses. I got the logic for this now and just wanna share it with everyone.

BEGIN
DECLARE @LastCode INT,
@LastDesc VARCHAR(512)

SELECT Code, Seq_no, Text_desc
INTO #S
FROM S1
ORDER BY Code, Seq_no

SET @LastDesc = ''

UPDATE #S
SET @LastDesc = CASE WHEN Seq_no = 0
THEN Text_desc
ELSE @LastDesc +Text_desc END,

Text_desc = CASE WHEN Seq_no = 0 THEN Text_desc
ELSE @LastDesc +Text_desc END

SELECT Code, Text_desc
FROM #S
GROUP BY Code HAVING Seq_no=max(Seq_no)

END

[mike_bike_kite]

A list funtion?? that's a new one on me!!! does it provide any ordering like the mysql function? I couldn't find the syntax on the web, would it be similar to:

select id, list(strings)
from table
group by id

I wasn't keen on supplying my non-list solution as it looked like an assignment to me.

[r937]

Originally Posted by mike_bike_kite

I couldn't find the syntax on the web...

what about the link i provided?

yeah, you understand the syntax -- it's an aggregate function

works exactly like mysql's GROUP_CONCAT

i expect all database systems will eventually implement something like it, because it's so bloody useful

we get requests for it in sql server, access, and other forums all. the. time.

[mike_bike_kite]

Originally Posted by r937

what about the link i provided?

yeah, you understand the syntax -- it's an aggregate function

works exactly like mysql's GROUP_CONCAT

Sorry missed the link. The only small issue seems to be it doesn't allow you to order the list as the MySQL group_concat does. So I'm not sure if it will help in this particular case. Must admit I'll definitly be using it in future though.

Originally Posted by madhudvm

Thanks for your responses. I got the logic for this now and just wanna share it with everyone.

Just to point out you're missing adding the commas between each list. You'll need to not add one for the first item though. Also you're only adding one list to another here so you're missing the 1579 2 T record I believe.

I don't have Sybase at home so forgive me if it has a few syntax errors but I think something like this might work:

insert T1 ( Code, Seq_no, Text_desc )
select distinct Code, null, null from S1

while @@rowcount != 0
    update T1
    set     Seq_no = s.Seq_no
            Text_desc = isnull( t.Text_desc + ', ', '' ) + s.Text_desc
    from    T1 t, S1 s
    where   t.Code = s.Code
            and s.Seq_no = ( 
                      select min( Seq_no )
                      from   S1 nxt
                      where  nxt.Code = t.Code
                               and ( nxt.Seq_no > isnull( t.Seq_no,-99 ) )
                      )
end

select * from T1

[karthi_syb]

Syntax

LIST ( string-expression | DISTINCT column-name )

Parameters
string-expression

A string, usually a column name. For each row, the expression's value is added to the comma-separated result.


DISTINCT column-name

The name of a column that you are using in the query. For each unique value of that column, the value is added to the comma-separated result.


Examples
The following statement returns the value 48 Kennedy Court,54 School Street.


SELECT LIST( street ) FROM employee
WHERE emp_fname = 'Thomas'

Usage
NULL values are not added to the list. Returns NULL for a group containing no rows.

Standards and compatibility

SQL/92

Vendor extension.


Sybase

Not supported in Adaptive Server Enterprise.