How can I concatenate all rows in sql

Question

I got the following exercices: Determine the average distance for all addresses, and concat the average distances by address in a csv like column “CSV_Avg” as can be seen in table Address _Distances.

This is how the table looks

This is what query should do

So far i've done this but i can't find a way how to concatenate all rows in a single column

This is how my query looks like

SELECT DISTINCT 

      [id_address]    
      ,sum([distance])/count([distance]) as "AVGDistance"
      ,CONCAT([id_address],'=',[distance]) as "CSV_AVG"
  FROM [Test].[dbo].[View_2]
  WHERE [id_address] IS NOT NULL

  Group by [id_address],[distance]

Usually no need to do SELECT DISTINCT when you do GROUP BY. – jarlh Dec 21 '17 at 13:50 — jarlh, Dec 21 '17 at 13:50

score 0 · Accepted Answer · answered Dec 21 '17 at 14:04

You can do this:

WITH CTE
AS
(
  SELECT 
      [id_address]    
      ,sum([distance])/count([distance]) as "AVGDistance"
  FROM view_2
  WHERE [id_address] IS NOT NULL
  Group by [id_address]
)
SELECT *, (SELECT  STUFF((SELECT ',' + CONCAT([id_address],'=',AVGDistance) 
                         FROM CTE
                        FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                          , 1, 1, '')) AS CSV_AVG
FROM CTE;

demo

This will give you the same exact results that you are looking for:

| id_address | AVGDistance |                               CSV_AVG |
|------------|-------------|---------------------------------------|
|         21 |       72.84 | 21=72.840000,35=49.195000,95=1.690000 |
|         35 |      49.195 | 21=72.840000,35=49.195000,95=1.690000 |
|         95 |        1.69 | 21=72.840000,35=49.195000,95=1.690000 |

Hey, i have a question how its posible to update more rows at the same time based on value of another column? — johnnyReed, Dec 21 '17 at 16:20
@johnnyReed - Yes sure, you can do this with no problem using only an update statement. You can also update from another table or view. Add your question and send me the link and I will help you. — , Dec 22 '17 at 07:10

How can I concatenate all rows in sql

1 Answers1