Answered by:
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
Wednesday, January 10, 2018 11:28 AM
Answers
-
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
- Edited by Sandeep Prajapati Wednesday, January 10, 2018 11:35 AM
- Proposed as answer by pituachMVP Wednesday, January 10, 2018 12:33 PM
- Marked as answer by Oscar_Wu Wednesday, January 31, 2018 7:48 AM
Wednesday, January 10, 2018 11:34 AM -
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
MyBlogWednesday, January 10, 2018 2:11 PM
All replies
-
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
- Edited by Sandeep Prajapati Wednesday, January 10, 2018 11:35 AM
- Proposed as answer by pituachMVP Wednesday, January 10, 2018 12:33 PM
- Marked as answer by Oscar_Wu Wednesday, January 31, 2018 7:48 AM
Wednesday, January 10, 2018 11:34 AM -
Hi ,
Why not using the FOR XML PATH as suggested ?
Regards, David .
- Proposed as answer by pituachMVP Wednesday, January 10, 2018 12:33 PM
Wednesday, January 10, 2018 11:35 AM -
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 PageWednesday, January 10, 2018 11:40 AM -
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
- Proposed as answer by Naomi N Wednesday, January 10, 2018 1:27 PM
Wednesday, January 10, 2018 1:25 PM -
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
MyBlogWednesday, January 10, 2018 2:11 PM -
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 MSDNFSF@microsoft.com.Thursday, January 11, 2018 1:59 AM