September 21, 2008 at 9:33 am
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
September 21, 2008 at 5:51 pm
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
Change is inevitable... Change for the better is not.
September 22, 2008 at 12:25 am
or refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
Failing to plan is Planning to fail
September 22, 2008 at 3:36 am
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
September 22, 2008 at 3:47 am
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/61537September 22, 2008 at 6:04 am
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
---
September 22, 2008 at 8:15 am
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