none
How can I convert values in a column into comma delimited values in a field in as a single row? RRS feed

  • Question

  • I'm using SQL Server 2014.

    I have a table that has about 8k rows.

    When I run the statement

     

    Select *

      FROM UserTable

      WHERE EmployeeName = 'John_Ross'

      AND AppLocation = '233'

      AND ServerName = 'GM23'

    I get the following result:

    EmployeeName

    AppName

    AppUID

    AppCode

    AppLocation

    StoreTitile

    LoadDate

    ServerName

    John_Ross

    CFFMS

    1001

    8AFFK

    233

    NULL

    00:00.0

    GM23

    John_Ross

    CFFMS

    1002

    8AGGK

    233

    NULL

    00:00.0

    GM23

    John_Ross

    CFFMS

    1003

    8ANNK

    233

    NULL

    00:00.0

    GM23

    John_Ross

    CFFMS

    1004

    8ALLK

    233

    NULL

    00:00.0

    GM23

    John_Ross

    CFFMS

    1005

    8ABBK

    233

    NULL

    00:00.0

    GM23

    For any given EmployeeName, and any given AppLocation, and any given ServerName,  I need  AppCode column values to appear in the single field on one row as a comma delimited value like this:

    AppCode

    8AFFK, 8AGGK, 8ANNK, 8ALLK, 8ABBK

    Final output of all rows should look like this:

    EmployeeName

    AppName

    AppUID

    AppCode

    AppLocation

    StoreTitile

    LoadDate

    ServerName

    John_Ross

    CFFMS

    1001

    8AFFK,8AGGK…

    233

    NULL

    00:00.0

    GM23

    If there happen to be duplicate values in the Appcode column, only the distinct value should appear.  No duplicate values should appear in the comma delimited field. 

    Note:  The AppUID doesn’t matter so any single one of the AppUID values can appear in the AppUID field in the final output.  It can be the lowest AppUID number, the highest, or any.

    I hope this request is clear and someone can help quickly.  Thanks.




    • Edited by Cmagnus Monday, December 23, 2019 9:10 PM
    Monday, December 23, 2019 7:34 PM

All replies

  • try this..

    declare @Test Table(sno int,scity varchar(20),id int)
    
    Insert @Test(sno,scity,id)
    Values
    (1,'Chicago',100),(1,'Detriot',101)
    ,(2,'Memphis',200),(2,'Nashville',201)
    ,(3,'New York',300),(3,'Dallas',301)
    
    
    select distinct sno ,MAX(id) OVER (PARTITION BY sno ORDER BY sno) AS Id,
    STUFF((Select ','+Scity
    from @Test T1
    where T1.sno=T2.sno
    FOR XML PATH('')),1,1,'') AS [Places] FROM @Test T2


    Hope it Helps!!

    Monday, December 23, 2019 7:41 PM
  • I'm a little new at this.  Can you reply using the data values in my post?  It would make it easier for me to move forward.
    Monday, December 23, 2019 7:43 PM
  • declare @Test Table(EmployeeName VARCHAR(200),AppName varchar(20),AppUID INT,AppCode VARCHAR(20),AppLocation int,StoreTitile int,LoadDate varchar(200),ServerName varchar(20))
    
    Insert @Test
    Values
    ('John_Ross','CFFMS',1001,'8AFFK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1002,'8AGGK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1003,'8ANNK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1004,'8ALLK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1005,'8ABBK',233,NULL,'00:00.0','GM23')
    
    select distinct T2.EmployeeName, T2.AppName,MAX(AppUid) OVER (PARTITION BY EmployeeName) AS Id,
    STUFF((Select ','+AppCode
    from @Test T1
    where T1.EmployeeName=T2.EmployeeName
    FOR XML PATH('')),1,1,'') AS [Places],AppLocation,StoreTitile,LoadDate,ServerName FROM @Test T2
    
    
    


    Hope it Helps!!

    • Proposed as answer by Lily Lii Monday, December 30, 2019 1:17 AM
    Monday, December 23, 2019 7:58 PM
  • CREATE TABLE mytable(
       EmployeeName VARCHAR(9) NOT NULL  
      ,AppName      VARCHAR(5) NOT NULL
      ,AppUID       INTEGER  NOT NULL
      ,AppCode      VARCHAR(5) NOT NULL
      ,AppLocation  INTEGER  NOT NULL
      ,StoreTitile  VARCHAR(4)
      ,LoadDate     VARCHAR(7) NOT NULL
      ,ServerName   VARCHAR(4) NOT NULL
    );
    INSERT INTO mytable(EmployeeName,AppName,AppUID,AppCode,AppLocation,StoreTitile,LoadDate,ServerName) VALUES
     ('John_Ross','CFFMS',1001,'8AFFK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1002,'8AGGK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1003,'8ANNK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1004,'8ALLK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1005,'8ABBK',233,NULL,'00:00.0','GM23');
    
    --SQL Server 2017,2019
    select EmployeeName,AppName
    ,Min(AppUID) AppUID
    ,STRING_AGG (AppCode, ',') AS AppCodes 
    ,AppLocation,StoreTitile,LoadDate,ServerName
    from mytable
    
    group by EmployeeName,AppName 
    ,AppLocation,StoreTitile,LoadDate,ServerName 
    
    
    drop TABLE mytable

    Monday, December 23, 2019 8:11 PM
    Moderator
  • My table has over 8k rows with lots of other EmployeeName records.   Is there an easy way to instantly generate all of the insert values needed?  There's no way I can do them all manually.

    ('John_Ross','CFFMS',1001,'8AFFK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1002,'8AGGK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1003,'8ANNK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1004,'8ALLK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1005,'8ABBK',233,NULL,'00:00.0','GM23')

    Monday, December 23, 2019 8:11 PM
  • My table has over 8k rows with lots of other EmployeeName records.   Is there an easy way to instantly generate all of the insert values needed?  There's no way I can do them all manually.

    ('John_Ross','CFFMS',1001,'8AFFK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1002,'8AGGK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1003,'8ANNK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1004,'8ALLK',233,NULL,'00:00.0','GM23'),
    ('John_Ross','CFFMS',1005,'8ABBK',233,NULL,'00:00.0','GM23')These 

    These inserts scripts are just for the sample data you provided. You are suppose to provide them for us.

    You don't need them for your real thing because you already have the table.

    --SQL Server 2017,2019
    select EmployeeName,AppName
    ,Min(AppUID) AppUID
    ,STRING_AGG (AppCode, ',') AS AppCodes 
    ,AppLocation,StoreTitile,LoadDate,ServerName
    from 
    
    (
    Select EmployeeName,AppName,AppUID, AppCode
    ,AppLocation,StoreTitile,LoadDate,ServerName 
      FROM UserTable  
      WHERE EmployeeName = 'John_Ross'
      AND AppLocation = '233'
      AND ServerName = 'GM23'
    
    ) t
    
    group by EmployeeName,AppName 
    ,AppLocation,StoreTitile,LoadDate,ServerName 


    Monday, December 23, 2019 8:32 PM
    Moderator
  • Ues the dashboard.
    Monday, December 23, 2019 8:37 PM
  • @Jingyang Li I get an error saying 

    "'STRING_AGG' is not a recognized built-in function name."

    I'm using SQL Server 2014 Management Studio so STRING_AGG can't be used.

    Is there a substitute function I can use in place of STRING_AGG?




    • Edited by Cmagnus Monday, December 23, 2019 9:15 PM
    Monday, December 23, 2019 8:55 PM
  • String_agg is a new function for SQL Server 2017 or 2019.

    You can try this:

    CREATE TABLE mytable(
       EmployeeName VARCHAR(9) NOT NULL  
      ,AppName      VARCHAR(5) NOT NULL
      ,AppUID       INTEGER  NOT NULL
      ,AppCode      VARCHAR(5) NOT NULL
      ,AppLocation  INTEGER  NOT NULL
      ,StoreTitile  VARCHAR(4)
      ,LoadDate     VARCHAR(7) NOT NULL
      ,ServerName   VARCHAR(4) NOT NULL
    );
    INSERT INTO mytable(EmployeeName,AppName,AppUID,AppCode,AppLocation,StoreTitile,LoadDate,ServerName) VALUES
     ('John_Ross','CFFMS',1001,'8AFFK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1002,'8AGGK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1003,'8ANNK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1004,'8ALLK',233,NULL,'00:00.0','GM23')
    ,('John_Ross','CFFMS',1005,'8ABBK',233,NULL,'00:00.0','GM23');
    
    
    SELECT t1.EmployeeName,t1.AppName,Min(t1.AppUID) AppUID,
    t1.EmployeeName,t1.AppName,t1.AppLocation
    ,t1.StoreTitile,t1.LoadDate,t1.ServerName
    ,Stuff(( SELECT ',' + t2.AppCode
               FROM mytable t2
              WHERE 
    		  t1.EmployeeName=t2.EmployeeName
    		  AND t1.AppName=t2.AppName
    		  AND t1.AppLocation=t2.AppLocation 
    		  
    		  AND t1.LoadDate=t2.LoadDate
    		  AND t1.ServerName=t2.ServerName
              ORDER BY t2.AppUID
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS AppCodes
      FROM mytable t1
     GROUP BY t1.EmployeeName,t1.AppName
     ,t1.AppLocation,t1.StoreTitile
     ,t1.LoadDate,t1.ServerName;
    
    
    drop TABLE mytable

    For your real table:

    ;with mycte as (
    
    Select EmployeeName,AppName,AppUID, AppCode
    ,AppLocation,StoreTitile,LoadDate,ServerName 
      FROM UserTable  
      WHERE EmployeeName = 'John_Ross'
      AND AppLocation = '233'
      AND ServerName = 'GM23'
      )
    SELECT t1.EmployeeName,t1.AppName,Min(t1.AppUID) AppUID,
    t1.EmployeeName,t1.AppName,t1.AppLocation
    ,t1.StoreTitile,t1.LoadDate,t1.ServerName
    ,Stuff(( SELECT ',' + t2.AppCode
               FROM mycte  t2
              WHERE 
    		  t1.EmployeeName=t2.EmployeeName
    		  AND t1.AppName=t2.AppName
    		  AND t1.AppLocation=t2.AppLocation 
    		 
    		  AND t1.LoadDate=t2.LoadDate
    		  AND t1.ServerName=t2.ServerName
              ORDER BY t2.AppUID
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS AppCodes
      FROM mycte t1
     GROUP BY t1.EmployeeName,t1.AppName
     ,t1.AppLocation,t1.StoreTitile
     ,t1.LoadDate,t1.ServerName;



    Monday, December 23, 2019 9:15 PM
    Moderator
  • I get an error saying 

    "'STRING_AGG' is not a recognized built-in function name."

    I'm using SQL Server 2014 Management Studio so STRING_AGG can't be used.

    Is there a substitute function I can use in place of STRING_AGG?



    try my code above. that should work in sql 2014 version.

    note: your sql server version matters not ssms.


    Hope it Helps!!

    Monday, December 23, 2019 9:15 PM
  • Hi Cmagnus,

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community.

    --please try this
    select EmployeeName,AppName,max(AppUID) AppUID,
         stuff((
            select ',' + u.AppCode
            from test1 u
            where u.AppCode = AppCode
            order by u.AppCode
            for xml path('')
          ),1,1,'') as Appcode,
    	AppLocation,StoreTitile,LoadDate,ServerName
    from test1
    where EmployeeName = 'John_Ross'
      and AppLocation = '233'
      and ServerName = 'GM23'
    group by EmployeeName,AppName,AppLocation,StoreTitile,LoadDate,ServerName

    Best Regards,

    Lily


    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

    • Proposed as answer by Lily Lii Monday, December 30, 2019 1:17 AM
    Tuesday, December 24, 2019 3:29 AM
  • Hi Cmagnus,

    Do the answers above help you? If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community.

    Best Regards,

    Lily


    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

    Wednesday, December 25, 2019 8:51 AM

  • Please read any book on RDBMS and then read a book on SQL. In the relational model we have what is called the information rule from Dr. Codd. It states that all information and relationships are shown as scalar values in the columns of the rows of tables. A comma separated list is not relational because it's not scalar or atomic. Also, if you like some really dull reading. Get a good book on SQL and you'll see that a field refers to part of a scalar value., Such as the year,, month or day in a date column. You're still trying to write procedural code, using old-style file structures and you haven't yet learned to think relationally.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 27, 2019 3:28 AM