I have an Oracle table with data in a form:

USER_ID |   Group1  |   Group2  |   Group3  |
---------------------------------------------
id1     |   YES |   NO  |   NO  |  
id1     |   NO  |   YES |   NO  |
id1     |   NO  |   NO  |   YES |
id2     |   NO  |   NO  |   NO  |
id2     |   YES |   NO  |   NO  |
id2     |   NO  |   NO  |   NO  |

I want to see it like this after select:

USER_ID |   Group1  |   Group2  |   Group3  |
---------------------------------------------
id1     |   YES |   YES |   YES |  
id2     |   YES |   NO  |   NO  |

Group by USER_ID column and show on a single line all accessible groups for single user_id. I do not want to write scripts. I have found some topics which suggest using MIN() and MAX() then group by USER_ID

I have tried:

select 
MIN(USER_ID) KEEP(DENSE_RANK FIRST ORDER BY Group1) USER_ID,
MIN(Group1),
MIN(Group2),
MIN(Group3)
FROM TABLE_NAME
GROUP BY USER_ID;

But it gives me an error: query is not group by form.

CodePudding user response：

You don't need an analytic query as the first term (though that doesn't actually error), you just need the actual USER_ID column; and you should be using MAX() rather than MIN() because 'YES' will sort alphabetically after 'NO':

SELECT 
  USER_ID,
  MAX(Group1),
  MAX(Group2),
  MAX(Group3)
FROM TABLE_NAME
GROUP BY USER_ID;

or with column aliases:

SELECT 
  USER_ID,
  MAX(Group1) AS Group1,
  MAX(Group2) AS Group2,
  MAX(Group3) AS Group3
FROM TABLE_NAME
GROUP BY USER_ID;

db<>fiddle

Group by USER_ID column and show on a single line all accessible groups for single user_id

That could be read as wanting a list of groups with any 'YES' value, rather than a column for each group. That could be achieved with conditional aggregation (which only keeps 'YES' in this case), an unpivot, and then string aggregation:

SELECT 
  user_id,
  LISTAGG(group_id, ',') WITHIN GROUP (ORDER BY group_id) AS groups
FROM (
  SELECT
    user_id,
    MAX(CASE WHEN group1 = 'YES' THEN group1 END) AS group1,
    MAX(CASE WHEN group2 = 'YES' THEN group2 END) AS group2,
    MAX(CASE WHEN group3 = 'YES' THEN group3 END) AS group3
  FROM table_name
  GROUP BY user_id
)
UNPIVOT (flag FOR group_id IN (group1 AS 'Group1', group2 AS 'Group2', group3 AS 'Group3'))
GROUP BY user_id

db<>fiddle showing the intermediate transformations.