Group two rows into one in TOAD data point

Question

My current result set is below:

Case_ID    |   Action_name    |   Status_code

1             John                  Valid

1             Peter                 Valid

I want this to be

Case_ID      |    Action_name   |    Status_Code

1              John, Peter              Valid

or it can also be displayed as

Case_ID       |  Action_name1   |   Action_name2   |   Status_code

1                   John               peter              Valid

Any possible information would be greatly helpful. I tried the Pivot function but for some reasons, I couldn't get it right. I know we can do this in SQL server but I'm new to TOAD data point.

Answer 1

You didn't specify database you use. If it is Oracle, then LISTAGG function might help:

SQL> -- This is your current output:
SQL> with test (case_id, action_name, status_Code) as
  2     (select 1, 'John', 'valid' from dual union all
  3      select 1, 'Peter', 'valid' from dual
  4     )
  5  -- Adjust it using this:
  6  select
  7    case_id,
  8    listagg(action_name, ', ') within group (order by action_name) action_name,
  9    status_code
 10  from test
 11  group by case_id, status_code;

   CASE_ID ACTION_NAME                    STATU
---------- ------------------------------ -----
         1 John, Peter                    valid

SQL>

Otherwise, see whether your database offers such an aggregation function; I know some of them do.