How to implent the following T-SQL

Question

how to implement the following case with SQL Server, I am encountered the same issue now with SQL Server:

https://stackoverflow.com/questions/15742716/how-to-get-column-values-in-one-comma-separated-value

Thank you very much

Answer 1

Hi,

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtable t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtable t;

You can achieve your result in SQL Server using this query.

---

Sandeep Prajapati

Answer 2

Hi ,

Why not using the FOR XML PATH as suggested ?

---

Regards, David .

Answer 3

The posted link already has the solution specified

Did you try it?

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

In SQL Server 2017, one can use STRING_AGG (https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) to concatenate string values. The suggested XML PATH method needs to be used in earlier versions.

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 5

Hi,

I believe same script will give you same result.

CREATE TABLE #T1(
ID INT,
USERNAME VARCHAR(10),
DEPARTMENT VARCHAR(15))

INSERT INTO #T1
VALUES(1  , 'User1',     'Admin')
INSERT INTO #T1
VALUES(2  , 'User1',     'Accounts')
INSERT INTO #T1
VALUES(3  , 'User2',     'Finance')
INSERT INTO #T1
VALUES(4  , 'User3',     'Sales')
INSERT INTO #T1
VALUES(5  , 'User3',     'Finance')




SELECT DISTINCT T.USERNAME, 
		  STUFF((SELECT distinct ',' + T1.DEPARTMENT
				 FROM #T1 T1
				 WHERE T.USERNAME = T1.USERNAME				
					FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)')
				,1,1,'') DEPT
		FROM #T1 T

I hope this is helpful

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid

---

MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
MCSA: SQL Server 2012/2014
MCTS: SQL Server Administration/Development
MyBlog

Answer 6

Hi qing,

You shared link has already got a solution. And it should work for you in SQL Server. 

If you got any issues when running the FOR XML PATH Method. Please share us your table structure and some sample data along with the error messages. So that we can provide targeted suggestions.

Thanks,
Xi Jin.

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].