Combining Rows into single one

  • Hi ,

    Could you help me in solving the problem

    Input:

    ------

    Name No status Address

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

    suresh 1 a Africa

    suresh 1 a America

    suresh 1 a England

    suresh 1 a India

    Output should be

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

    Name No status Address

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

    suresh 1 a AfricaAmericaEnglandIndia

    Thanls and Regards

    Suresh

  • Yes... please see the following link which is a bit of a "how to" along with some things to avoid when doing this type of concatenation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • or refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254


    Madhivanan

    Failing to plan is Planning to fail

  • Hi ,

    Iam sorry for not posting the question in a proper way

    Input:

    ------

    Id Name status Address

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

    1 suresh a Africa

    1 suresh a America

    1 suresh a England

    1 suresh a India

    1 suresh a Mexico

    CREATE TABLE Test

    (

    ID int,

    NAME VARCHAR(20),

    STATUS VARCHAR(20),

    ADDRESS VARCHAR(20)

    )

    insert into Test VALUES (1,'SURESH','A','AMERICA ')

    insert into Test VALUES (1,'SURESH','A','AUSTRALIA ')

    insert into Test VALUES (1,'SURESH','A','AFRICA ')

    insert into Test VALUES (1,'SURESH','A','INDIA ')

    insert into Test VALUES (1,'SURESH','A','MEXICO ')

    The output should be

    Id Name status Address

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

    1 SURESH A AMERICA AUSTRALIA AFRICA INDIA MEXICO

    Can you help me in solving the above problem

  • WITH CTE AS (

    SELECT DISTINCT ID,NAME,STATUS

    FROM Test)

    SELECT c.ID,c.NAME,c.STATUS,

    (SELECT t.ADDRESS AS "text()" FROM Test t WHERE t.ID=c.ID ORDER BY t.ADDRESS FOR XML PATH(''))

    FROM CTE c

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Hi suresh,

    Try this one also.

    SELECT

    fldID,fldName,fldStatus,[India]AS Addresss,[USA]AS Addresss,[Denmark]AS Addresss,[Spain] AS Addresss

    FROM

    (SELECT fldID,fldName,fldStatus,fldAddress FROM #Test) T

    PIVOT

    (

    MAX(fldAddress)

    FOR fldAddress IN ([India],[USA],[Denmark],[Spain])

    ) AS Test

    ---

  • Try this:

    SELECT ID

    ,[NAME]

    ,STATUS

    ,STUFF(

    (

    SELECT '' + B.ADDRESS

    FROM #Test B

    WHERE B.ID = A.ID

    FOR XML PATH(''))

    ,

    1, 0, '')

    FROM #Test A

    GROUP BY ID

    ,[NAME]

    ,STATUS

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

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