Concaten fields from a subquery

Question

Concaten fields from a subquery

Navigation

#1 by (1 votes)

1

I have the following query in sql sever 2014

CREATE TABLE #TEMPTABLE
(
    ID       INT,
    DATACOLUM    VARCHAR(5)
)
INSERT INTO #TEMPTABLE VALUES 
(1,'A'),(1,'B'),(1,'C'),(2,'D'),(3,'E'),(4,'F')

SELECT 
    TB.ID,
    STUFF((SELECT ',' + DATACOLUM FROM TB  FOR XML PATH ('')),1,1,'') AS CONCATSTUFF
FROM(
SELECT *
FROM #TEMPTABLE) AS TB
GROUP BY TB.ID

The expected result is that you can concatenate the "DATACOLUM" column that is obtained from the sub query

SELECT *
FROM #TEMPTABLE) AS TB

the sub-query is just an example since it is expected to have a more complex sub-query

sql sql-server sql-server-2014

asked by DEVJ 24.05.2017 в 19:18

source

1 answer

Fill in form data from an object - Using Javascript create tunnel connection via ssh (JAVA) to type commands in PUTTY console

score 1 · Answer 1

You're pretty close, the query for the concatenation would be the following:

SELECT  t.Id, 
        ConcatStuff = STUFF((SELECT ',' + CONVERT(VARCHAR(12), DATACOLUM) 
                             FROM #TEMPTABLE 
                             WHERE ID = t.ID
                             FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'),1,1,'')
FROM #TEMPTABLE AS t
GROUP BY t.Id;

The result is:

╔════╦═════════════╗
║ Id ║ ConcatStuff ║
╠════╬═════════════╣
║  1 ║ A,B,C       ║
║  2 ║ D           ║
║  3 ║ E           ║
║  4 ║ F           ║
╚════╩═════════════╝