views:

53

answers:

1

I have a table where the data are like

Data 

 a 
 b 
 c 

I need to write a SQL query to bring the following output

Data 

abc 

How to do the same by using in SQL Server 2000 Thanks

+1  A: 

I don't know how/if it can be done with XML RAW. This approach works in SQL2000 though.

DECLARE @Data varchar(8000)
set @Data =''
select @Data = @Data + Data
FROM @t 
ORDER BY Data

SELECT @Data

Edit Oh I've just seen your other question where Cade gave you a link. Doesn't KM's answer on that link work for you?

KM's test query

--combine parent and child, children are CSV onto parent row
CREATE TABLE #TableA (RowID int, Value1 varchar(5), Value2 varchar(5))
INSERT INTO #TableA VALUES (1,'aaaaa','A')
INSERT INTO #TableA VALUES (2,'bbbbb','B')
INSERT INTO #TableA VALUES (3,'ccccc','C')

CREATE TABLE #TableB (RowID int, TypeOf varchar(10))
INSERT INTO #TableB VALUES (1,'wood')
INSERT INTO #TableB VALUES (2,'wood')
INSERT INTO #TableB VALUES (2,'steel')
INSERT INTO #TableB VALUES (2,'rock')
INSERT INTO #TableB VALUES (3,'plastic')
INSERT INTO #TableB VALUES (3,'paper')

SELECT
    a.*,dt.CombinedValue
    FROM #TableA        a
        LEFT OUTER JOIN (SELECT
                             c1.RowID
                                 ,STUFF(REPLACE(REPLACE(
                                          (SELECT 
                                               ', ' + TypeOf as value
                                               FROM (SELECT
                                                         a.RowID,a.Value1,a.Value2,b.TypeOf
                                                         FROM #TableA                 a
                                                             LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                                    ) c2
                                               WHERE c2.rowid=c1.rowid
                                               ORDER BY c1.RowID, TypeOf
                                               FOR XML RAW
                                          )
                                         ,'<row value="',''),'"/>','')
                                   , 1, 2, '') AS CombinedValue
                             FROM (SELECT
                                       a.RowID,a.Value1,a.Value2,b.TypeOf
                                       FROM #TableA                 a
                                           LEFT OUTER JOIN #TableB  b ON a.RowID=b.RowID
                                  ) c1
                             GROUP BY RowID
                        ) dt ON a.RowID=dt.RowID
Martin Smith
Actually may be I am not able to put the solution of KM properly in my program.. could you pls help
learner
I don't have SQL2000 installed. Can you confirm whether or not the code from KM's answer works on SQL2000 when you run it unmodified?
Martin Smith
what is this '<row value="',''),'"/>'
learner
@learner Something that SQL Server puts into the raw XML that is being replaced with an empty string. Does KM's code above work fine in SQL2000 (It definitely does in SQL2005)
Martin Smith