Tutorial :How to concatenate using in sql server



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
Previous
Next Post »