Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: concatenate multiple rows into a single row

    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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You may use the CONVERT function

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2008
    Posts
    2
    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

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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:
    Code:
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote 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.

    Quote 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:

    Code:
    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

  9. #9
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •