Post reply

It is hard to code to process

  • December 30, 2011 at 3:04 pm

    #252797

    I have a table need to be processed as below:

    ID Date(text) Sequence

    ---------------------------

    11 10/11/2011 1

    11 11/20/2011 2

    22 05/15/2011 1

    22 09/24/2011 2

    22 11/22/2011 3

    How to code to out put like below?

    ID Result

    ---------------------------

    11 10/11/2011-11/20/2011

    22 05/15/2011-09/24/2011-11/22/2011

    (Table includes about 5000 records and some ID sequence number > 15)

  • December 30, 2011 at 3:21 pm

    #1427140

    Please have a look at "Concatenating Row Values in Transact-SQL" in my signature.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • January 4, 2012 at 1:45 pm

    #1428644

    Thank you. This is really helpful.

  • January 4, 2012 at 2:18 pm

    #1428678

    I just did something very similar this morning. I was combining emails from team members into one column having them separated by commas. I used the xml.value command to get what I wanted. I am not sure about performance on a large table, but it worked for what I needed.

    rowCte as

    (

    select *, ROW_NUMBER() over (partition by id order by sequence) rownum

    from yourTable

    )

    select distinct id,(

    select date + case when r1.rownum = (select MAX(rownum) from rowCte where id = r1.id)

    then '' else ' - ' end from rowCte r1

    where s1.id = s2.id

    for xml path(''),type).value('(.)[1]','varchar(max)')

    from rowCte r2

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

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