SQL Select Query with multiple records

Transact-SQL
1

Hi,
I have the following SQL statement and would like to display the results in 2 columns with ID and all the Category displayed in a single line separated by a comma.

Example:

1234 Admin, Manager, Sales
345 Sales
41234 Manager, Sales
6467 Manager, Sales
91234 Sales

Please use this SQL Statement for the data set:

DECLARE @TBL_EXAMPLE table
(
[ID] varchar(50),
[Category] varchar(25)
)

INSERT INTO @TBL_EXAMPLE ([ID], [Category])
VALUES

('1234', 'Manager'),
('1234', 'Sales'),
('345', 'Sales'),
('6467', 'Manager'),
('6467', 'Sales'),
('41234', 'Sales'),
('41234', 'Manager'),
('91234', 'Sales'),
('1234', 'Admin');

select * from @TBL_EXAMPLE

2 
select id,  
	STUFF( 
	        (       SELECT ',' + Category  
	                FROM @TBL_EXAMPLE t2  
	                WHERE t1.id=t2.id 
	                FOR XML PATH('') 
	        ), 
	        1, 
	        1,'' 
	      ) AS t 
	FROM @TBL_EXAMPLE t1
	GROUP BY ID
3

IF your working with SQL Server 2017 or better, STRING_AGG() works a real treat.

 SELECT  ID
        ,Categories = STRING_AGG(Category,', ') WITHIN GROUP (ORDER BY Category)
   FROM @TBL_EXAMPLE
  GROUP BY ID
  ORDER BY ID
;

REF: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15