Post reply

Mutlipe row concatenation

  • January 28, 2005 at 1:40 am

    #76226

    I want to concatenate muliptle row into single row. Is there a way without using cursor.

    e.g  Delivery Order, Item, Qty, DeilveryDate

          DO001, ITEM010, 100, 01-Jan-2005

          DO001, ITEM010, 150, 12-Jan-2005

    Want to concat. above 2 rows in one row...as D0001, ITEM010, 100~01-JAN-2005~150~12-JAN-2005....i have achived the above by using cursor. Is there any best way to get above result without using cursor or any optimised way ?. As cursor take time to process the data.

    Thanks in advance

    regards

    sara

     

     

     

     

  • January 28, 2005 at 3:35 am

    #539495

    There is no optimized way to do pivoting in Transact SQL - if you want it to be as optimal as possible, this should be done at the client. SQL isn't 'suitable' for this kind of operations.

    There are a few ways to do it anyway, though none that is pretty or especially performance friendly (ie the cursor way).

    /Kenneth

  • January 30, 2005 at 6:38 pm

    #539674

    Thanks Kenneth,

     

    As the above process being part of ETL program, there is no way to utilise this in the client side. Anywat thanks for u'r info.

    regards

    saravan

  • January 30, 2005 at 8:53 pm

    #539678

    Hi,

    You can try this solution

    Declare @longStr varchar(5000)

    Set @longStr=''

    Select @longStr=@longStr + col1+'~'+col1+'~'+col2+'~'+...+colN+'$'

    From Orders

    But this string may get truncated if your rowsize increases.

    -Hari

     

     

     

    Kindest Regards,

    Hari

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply