Question:
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
Solution:1
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 Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon