SQL Server Concatenate Query Results

Question

I am new to sql server. I have a query that retrieves the desired data from various tables. The result looks like this with other columns, that contain things like name, removed for simplicity.

I would like to wrap the query with a select to concatenate the result set into an new result set like this.

  id   xvalue
  1     x,y,z
  2     x,y,z
  3     x,y,z

I have tried to figure out how use the for xml path option and cannot seem to find the correct syntax.

Possible duplicate of http://stackoverflow.com/questions/15477743/listagg-in-sqlserver — Michael McGriff, Oct 29 '14 at 17:55
duplicate of http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings — Steve, Oct 29 '14 at 17:57
I have seen the other answers but I am trying to understand if you can do something like select values from ( select query ) as x and in the process get a distinct row for each id with concatenated values for xvalue. It appears that sql server requires the creation of a table then select from there. I am used to mysql concate function with in a query. — dutchlab, Oct 29 '14 at 18:11

Ruben Norori · Answer 1 · 2015-05-26T16:02:00.490

1

We have two tables in SQL one with columns (id,mdf,hist) another table with columns (id,hist). First table called historial, second table is resultado. Then we need to concat hist column when the mfn is the same and copy the result to the column hist in resultado table.

DECLARE @iterator INT

SET @iterator = 1

WHILE (@iterator < 100) /* Number of rows */

BEGIN

DECLARE @ListaIncidencias VARCHAR(MAX) = ''
SELECT @ListaIncidencias = @ListaIncidencias + ';' + p.hist
FROM historial p WHERE mfn = @iterator
SET @ListaIncidencias = SUBSTRING(@ListaIncidencias,2,LEN(@ListaIncidencias))
SELECT @ListaIncidencias as 'Incidencias'
INSERT INTO resultado(hist) VALUES(@ListaIncidencias) /* Insert results in new table */
SET @iterator = @iterator + 1

END

edited May 26 '15 at 16:02

answered May 26 '15 at 15:01

Ruben Norori

41
4

Please explain your answer, just posting code isn't clear for solving problems. – SuperBiasedMan May 26 '15 at 15:05
1

Sorry... I will improve my answer – Ruben Norori May 26 '15 at 15:14

score 0 · Answer 2 · answered Oct 29 '14 at 17:53

Something like this should do the trick for you. SQL Server should really just have an easy syntax for this. But it'll give you a comma and space separated list of the xValues.

SELECT id
,STUFF(
               (SELECT DISTINCT
                    ', ' + t.xvalue
                    FROM tableName t
                   WHERE t.id = t1.id
                    ORDER BY ', ' + t.xvalue
                    FOR XML PATH(''), TYPE
               ).value('.','varchar(max)')
               ,1,2, ''
          ) AS xvalue

FROM tableName t1
GROUP BY id

score 0 · Answer 3 · answered Oct 29 '14 at 17:59

0

select  id,Ids=Stuff((SELECT ',' + xvalue FROM t t1 WHERE t1.id=t.id   

 FOR XML PATH (''))
             , 1, 1, '' )
from t
GROUP BY id

FIDDLE

answered Oct 29 '14 at 17:59

Mihai

26,325
7
66
81

score 0 · Accepted Answer · answered Oct 29 '14 at 18:01

0

Try this

CREATE TABLE #Tmp
    (
      id INT ,
      xValue VARCHAR(10)
    )

INSERT  INTO #Tmp VALUES  ( 1, 'x' )
INSERT  INTO #Tmp VALUES  ( 1, 'y' )
INSERT  INTO #Tmp VALUES  ( 1, 'Z' )
INSERT  INTO #Tmp VALUES  ( 2, 'A' )
INSERT  INTO #Tmp VALUES  ( 2, 'B' )


SELECT  id ,
        ( STUFF(( SELECT DISTINCT
                            ',' + CAST(xValue AS VARCHAR(20))
                  FROM      #Tmp
                  WHERE     id = t.id
                FOR
                  XML PATH('')
                ), 1, 1, '') ) AS Data
FROM    #Tmp t
GROUP BY id

answered Oct 29 '14 at 18:01

Sanjay Goswami

1,386
6
13

This is the path I took for server 2008. For anyone new the insert statements can be your query inserting the values in the order you define them for the temporary table. I would also add a drop table tableName at the end of the query. It appears that server 2012 has a concatenate function. Not sure it that will work in this instance. Thanks Sanjay. – dutchlab Oct 29 '14 at 19:27

score 0 · Answer 5 · answered Mar 23 '17 at 04:35

There is very new functionality in Azure SQL Database and SQL Server 2016 to handle this exact scenario. Example:

select id, STRING_AGG(xvalue, ',') as xvalue
from some_table
group by id

STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx

SQL Server Concatenate Query Results

5 Answers5