-1

In SQL Server, I have a simple view called v_master_server_list.

Server entries have multiple group memberships, so the servers will be duplicated in the view for every group membership. I need to return only a single row with the distinct server names and for every group membership, I'd like this concatenated with some kind of delimiter like '|' or ','

I've come up with this (thanks for the link), and I had to scrub out the null entries otherwise the LEN and LEFT functions wouldn't work. Question is, how do I now return all entries (even if nothing is found in 'arms_group')?

select server, LEFT(column_names, LEN(column_names )-1) AS column_names
from cmdb.[dbo].v_master_server_list AS extern
CROSS APPLY
(
    select arms_group + ','
    FROM cmdb.[dbo].v_master_server_list AS intern
    where extern.server = intern.server
    FOR XML PATH('')
) pre_trimmed (column_names)

where arms_group is not null
GROUP BY server, column_names;
user219396
  • 57
  • 1
  • 7
  • You will need to select '`name`' `DISTINCT`, and also use `GROUP BY` command – Anna Gabrielyan Jan 31 '17 at 13:51
  • tried group by and get an error that it is not contained in either an aggregate function or the group by clause – user219396 Jan 31 '17 at 13:52
  • can you please show some codes, how did you run it? also give as the table structure – Anna Gabrielyan Jan 31 '17 at 13:53
  • Hi, here is the table structure of the view, hope its what you need -- https://postimg.org/image/ge3grtr59/ – user219396 Jan 31 '17 at 14:07
  • I'm trying this: select distinct server,arms_group from v_master_server_list group by server and getting: Msg 8120, Level 16, State 1, Line 1 Column 'v_master_server_list.ARMS_Group' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – user219396 Jan 31 '17 at 14:10

1 Answers1

1

You can use for xml to Concatenate Row Values in Transact-SQL

select distinct
    group_membership
  , servers = stuff(
    (
      select distinct '|'+i.server_name
        from v_master_server_list as i
        where i.group_membership = o.group_membership
        order by i.server_name
      for xml path (''), type).value('.','varchar(max)')
      ,1,1,'')
  from v_master_server_list as o

In Sql Server vNext, you can use string_agg()

SqlZim
  • 37,248
  • 6
  • 41
  • 59