Post reply

Row Merge with a Twist

  • September 8, 2011 at 10:44 am

    #244436

    Hi,

    Server: MSSQL 2008 R2

    I need to merge data from 2 fields into a single row.

    The problem is the fields are in the wrong order so I cant use COALESCE.

    Or maybe I can and just dont know how 🙂

    Data Table

    Customer

    Name |Area | City | Department

    John, Cape, Newlands, 1

    Peter, Natal, Durban, 1

    Suzi, Transvaal, Sandton, 2

    Output I would like :

    (Names and City's for Department 1)

    John, Newlands

    Peter, Durban

    Anyone have any ideas ?

  • September 8, 2011 at 10:47 am

    #1378759

    I use this, then I pivot on the presentation layer.

    SELECT

    T.name,

    STUFF((

    SELECT

    ',' + name

    FROM

    sys.columns C

    WHERE C.object_id = T.object_id

    ORDER BY

    name

    FOR

    XML PATH('')

    ) , 1 , 1 , '') As Columns_

    FROM sys.tables T

    ORDER BY name

  • September 8, 2011 at 10:49 am

    #1378760

    OOPS I Posted in the Server 2005 area.

    Sorry.

    Anyway ..

    Thanks for the reply. Will try that.

  • September 8, 2011 at 10:52 am

    #1378768

    zaleeu (9/8/2011)

    OOPS I Posted in the Server 2005 area.

    Sorry.

    Anyway ..

    Thanks for the reply. Will try that.

    Won't matter for this one :-).

  • September 8, 2011 at 11:01 am

    #1378774

    Nope, you lost me 🙂

    I dont see any place where you mention the name of the table. (Customers)

  • September 8, 2011 at 11:05 am

    #1378777

    zaleeu (9/8/2011)

    Nope, you lost me 🙂

    I dont see any place where you mention the name of the table. (Customers)

    Can't write this one for you. While this is a simple concatenation script, you just need to play with it to GET IT.

    No way around it. Took me hours to finally understand it. I'm sure you'll be faster than that.

    Hopefully you know your own tables / columns so you'll see what each bit does.

  • September 8, 2011 at 11:10 am

    #1378782

    Okay, time to figure this out then.

    Cheers !

    😛

  • September 8, 2011 at 11:11 am

    #1378783

    zaleeu (9/8/2011)

    Okay, time to figure this out then.

    Cheers !

    😛

    HTH, I'm not trying to be thick or anything. I just want to make sure you can support that code in production. Better to play with it in dev than in prod at 2 am with the cto calling ever 2 minutes :-D,

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

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