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)
Intro to Tally Tables and Functions -
or refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
MadhivananFailing 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