Post reply

Eliminate duplicate ID by concatenating string values from rows, where all columns except first one are dynamic

  • August 26, 2009 at 11:51 am

    #72866

    I have a table as follow-:

    TranID Field1 Field2

    1 f2

    1f1

    22f2

    22f1

    where field1, field2 are dynamic columns of type varchar

    There can be any number of columns Field1, Field2, Field3 so on

    My problem is, I want to remove repeated transactionid and show only once as below-:

    TranID Field1 Field2

    1 f1f2

    22f12f2

    Can anybody help me out on this as I am unable to figure out a way to handle this in dynamic columns.

    Thanks in advance.

  • August 26, 2009 at 11:54 am

    #1045054

    Look up "FOR XML" in BOL, it will help you concatenate those strings together, based on the TranID.

    Hope that helps,

    Cheers,

    J-F

  • August 26, 2009 at 10:48 pm

    #1045326

    Thanks J-F.

    I think this approach can solve my problem listed earlier.

    But I have come across another problem. When I try excuting following query on PUB database its showing me only half output. I can't see all rows converted to xml. M I misssing anything?

    Query-:

    SELECT store.stor_id as Id, stor_name as Name, sale.ord_num as OrderNo,

    sale.qty as Qty

    FROM stores store inner join sales sale on store.stor_id = sale.stor_id

    ORDER BY stor_name

    FOR XML AUTO

    Output-:

    <sale Ord

    I think editor will not show xml output I pasted under output.

    Could you please suggest here.

  • August 27, 2009 at 7:09 am

    #1045496

    Well actually, since you are on SQL server 2000, the solution will not work, it would have implied you can use for xml path(''), and it appeared in sql 2005...

    Really sorry for the mislead.

    Cheers,

    J-F

  • August 27, 2009 at 7:17 am

    #1045503

    This is all you need to know about concatenating strings

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/


    Clear Sky SQL
    My Blog[/url]

  • August 31, 2009 at 11:40 am

    #1047013

    I am facing few problems as all the columns except one are dynamic. But I am working on that.

    Thanks a lot.

  • August 31, 2009 at 11:49 am

    #1047019

    Hi,

    Above mentioned query is given expected output in sql 2000 too.

    I modified few settings in query analyzer in order to increase the maximum characters per column for result set and it worked.

    But it’s not working for dynamic column query of my application.

    I am trying to make it work.

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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