Join data on 2 columns in the same query

1697 views sql-server
2

I'm trying to join string data using FOR XML technique. In the result I would like to get 2 different columns with joined data. I can do 2 sub-selects to achieve that, but the problem is that the table I'll use in real will be just huge and scanning it 2 times will add a lot of overhead. Is it possible to join this data in a single run?

DROP TABLE #test;
GO

CREATE TABLE #test
(
    col1 VARCHAR(MAX)
  , col2 VARCHAR(MAX)
);
GO

INSERT INTO #test VALUES ('1value', '2value'), ('11value', '22value');

SELECT SUBSTRING((   SELECT t.col1 + ';' AS [text()]
                       FROM #test AS t
                     FOR XML PATH(''))
               , 1
               , 20000) AS Note

Expected result:

| col1          | col2          |
|1value;11value | 2value;22value|

answered question

have you tried LEFT JOIN ?

1 Answer

10

DROP TABLE #test; GO

CREATE TABLE #test ( col1 VARCHAR(MAX) , col2 VARCHAR(MAX) ); GO

INSERT INTO #test VALUES ('1value', '2value'), ('11value', '22value');

SELECT STUFF(( SELECT ';' + t.col1 AS [text()] FROM #test AS t FOR XML PATH('')) , 1 ,1 , '') AS Note, STUFF(( SELECT ';' + t.col2 AS [text()] FROM #test AS t FOR XML PATH('')) , 1 ,1 , '') AS Note2

posted this

Have an answer?

JD

Please login first before posting an answer.