Combining Rows into single one

Question

Combining Rows into single one

suresh_vc_mfc-784576

SSC Rookie

Points: 44
More actions
September 21, 2008 at 9:33 am

#212903

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

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

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

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)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

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

Madhivanan

Failing to plan is Planning to fail

suresh_vc_mfc-784576 SSC Rookie Points: 44 More actions · Answer 3

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

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

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 5

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

---

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

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