SQL Server Help - Denormalization and Concatenation of data (flattening to put into a report)

[sao123]

I have 2 tables as follows

[N_ID], [Name]
1, Scott
2, Wendy
3, Bill

[C_ID], [N_ID], [COLOR]
1, 1, BLUE
2, 1, RED
3, 1, YELLOW
4, 2, Orange
5, 2, Green
6, 3, Purple
7, 3, Pink

i need to write a query which will flatten table2 records (and concatenate them) in a join with table 1, so the output is like this:
[N_ID], [Name], [Colors]
1, Scott, BLUE+RED+YELLOW
2, Wendy, ORANGE+GREEN
3, BILL, Purple+Pink

and 1 limitation, I can't use a CTE.
I'm guessing I need some sort of outer/inner query or a while loop.

Select N_ID, Name,
( Select COLOR from T2 where T2.N_ID = T1.N_ID) AS COLOR
FROM T1

But how to I get the inner query to concatenate all into 1 field?

 

[Malogeek]

SELECT a.N_ID, a.Name, GROUP_CONCAT(b.Colors, SEPARATOR '+') AS colors FROM T1 LEFT JOIN T2 ON a.N_ID = b.N_ID GROUP BY a.N_ID

 

[KB]

GROUP_CONCAT is the way to go, except in SQL Server it was only added in 2017.

Here is a list of solutions in this exchange
https://stackoverflow.com/questions...t-mysql-function-in-microsoft-sql-server-2005

To add my own solution. In the past I have used UDFs to contenate the strings. The benefits is they are highly re-usable and make queries easy to read. The negative is they are a bit slow if you are returning thousands of rows.

CREATE FUNCTION [dbo].[GetColorsAsString] (@N_ID int)  
RETURNS varchar(400) AS  
BEGIN

DECLARE @color varchar(400)

Set @color = ''

SELECT @color = @color + Color + ','
FROM T2
WHERE T2 = @N_ID


RETURN @color

END
GO


to use:

Select N_ID, Name,dbo.GetColorAsString(N_ID) As Colors
FROM T1

 

[sao123]

I believe I have gotten this working using the STUFF command, but I still have some work to do because my listing now leads off with my delimiter