locked
How to implent the following T-SQL RRS feed

  • 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
    MyBlog

    • Proposed as answer by Xi Jin Tuesday, January 30, 2018 1:47 AM
    • Marked as answer by Oscar_Wu Wednesday, January 31, 2018 7:48 AM
    Wednesday, 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 Page

    Wednesday, 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
    MyBlog

    • Proposed as answer by Xi Jin Tuesday, January 30, 2018 1:47 AM
    • Marked as answer by Oscar_Wu Wednesday, January 31, 2018 7:48 AM
    Wednesday, 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