Additional column showing matching values within the same table

Question

I have a table with a ton of columns and rows. What I'm trying to do in my query statement is to create a new column that will show any additional rows that share the same attribute as that individual row.

Example - here is the original table

Name	Group
Jake	GroupA
Mike	GroupB
Sandy	GroupC
John	GroupD
Silvia	GroupA
Matt	GroupA

This is what im trying to output in oracle via my query

Name	Group	Associated People
Jake	GroupA	Matt, Silvia
Mike	GroupB	John
Sandy	GroupC	NULL
John	GroupB	Mike
Silvia	GroupA	Jake, Matt
Matt	GroupA	Jake, Silvia

my query is much larger than this with much more columns and rows but this is the gist of what im trying to do - There is actually over 2 million rows of data with 50 columns in the actual table I'm querying from that the data changes row numbers every couple hours and I would need to match multiple columns. Thanks

Here is a redaction/snip of the actual query but im getting a "not in group expression" error after adding the subquery

SELECT  ncp.host AS Host,
            substr(ncp.host,1,4) AS IP
            ncp.node_name AS Node_Name,
            substr(ncp.device,6) AS technology,
            ncp.grouping,
            (SELECT listagg(ncp1.node_name,',') within group(ORDER BY ncp1.node_name) FROM node.plan ncp1 WHERE ncp1.host_name = ncp.host_name
 AND ncp1.serving_group = ncp.serving_group
 AND ncp1.node_name != ncp.node_name) AS Sibling_Nodes
FROM node.plan ncp
LEFT JOIN node_sum.sum ncs ON ncp.site_id = ncs.site_id
WHERE ncp.flag = 'N'
GROUP BY ncp.host,
     ncp.node_name,
     ncp.device,
     ncp.grouping

Answer 1

You can use the ROW_NUMBER and LISTAGG analytic functions combined with CASE expressions:

SELECT name,
       group_name,
       CASE rn
       WHEN 1
       THEN LISTAGG(CASE WHEN rn > 1 THEN name END, ',')
              WITHIN GROUP (ORDER BY rn)
              OVER (PARTITION BY group_name)
       END AS associated_people
FROM   (
  SELECT name,
         group_name,
         ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY name) AS rn
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (Name, Group_name) AS
SELECT 'Jake',   'GroupA' FROM DUAL UNION ALL
SELECT 'Mike',   'GroupB' FROM DUAL UNION ALL
SELECT 'Sandy',  'GroupC' FROM DUAL UNION ALL
SELECT 'John',   'GroupB' FROM DUAL UNION ALL
SELECT 'Silvia', 'GroupA' FROM DUAL UNION ALL
SELECT 'Matt',   'GroupA' FROM DUAL;

Which outputs:

NAME	GROUP_NAME	ASSOCIATED_PEOPLE
Jake	GroupA	Matt,Silvia
Matt	GroupA	null
Silvia	GroupA	null
John	GroupB	Mike
Mike	GroupB	null
Sandy	GroupC	null

fiddle