0

I have two tables as follows:

Users

| Name   | TagsId |  
+--------+--------+  
| Bob    |  3     |  
| Jim    |  2     |  
| Bob    |  2     |   
| Frank  |  3     |  
| Jim    |  1     |

Tags

| Name    | Id  |
+---------+-----+
| web     |  1  |
| desktop |  2  |
| phone   |  3  |

My desired result is as follows:

| Name  | TagList        |
+-------+----------------+
| Bob   | desktop, phone |
| Jim   | desktop, web   |
| Frank | phone          |

I am able to accomplish this in SQL Server 2017 using the following query:

SELECT 
    u.name, STRING_AGG (t.name, ', ') as taglist 
FROM 
    users AS u       
LEFT JOIN 
    Tags AS t ON u.TagsId = t.Id 
GROUP BY 
    u.name;

But unfortunately my data is in a SQL Server 2016 server so cannot use the STRING_AGG function.

How can I accomplish this without STRING_AGG?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MarkG
  • 11
  • 1
    I think this: https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – nharrer Jan 31 '18 at 18:57
  • Thanks for the quick response @sean-lange but I cannot create temporary tables in the database as I do not have access. Any other thoughts/suggestions? – MarkG Jan 31 '18 at 20:58
  • Use a table variable then, DECLARE @table Table ( column INT ) – Owain Esau Jan 31 '18 at 21:13
  • I am guessing you didn't read, or at least understand, the solution found there. There is no need for a temp table. I don't see a single temp table anywhere in the question or any answers for the one I marked as a duplicate. – Sean Lange Jan 31 '18 at 21:16
  • @SeanLange, Yeah, I miss interpreted the table that was being created for the example as a necessary step where it is only needed to generate the data for the query. My bad. That said, that example only uses a single table but I think I can adapt it. – MarkG Jan 31 '18 at 21:52

0 Answers0