SQL for Data Engineers (oracle SQL)
Tags: sql, oraclesql, tutorial, plsql
Published: 2023-01-13 06:38:04

## Aggregating many columns into one column in SQL.

- I encountered a problem and I wanted to get the solution the quick and easy way, saving evergy and time, like all engineers out there.
- So I came up with this a kindof MVP (replace the P with S for solution), incase of better more optimum solution, please comment and share with the community.


```sql
SELECT  signatory_id 
       ,insert_on 
       ,insert_by 
       ,last_updated_on 
       ,last_updated_by 
       ,signatory_user 
       ,signatory_active 
       ,allow_weight_note 
       ,for_bu 
       ,allow_fcr 
       ,allow_whr 
       ,allow_hc
 FROM  signatories_t
      ;
```
The query above retrives this result, that we are working with tonight.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ja01i47smq8nvblc715e.png)

The goal we want to achieve is grouping the **ALLOW_*** columns into one called **DOCUMENTS** separated by a dilimiting character ('/') backslash.

Step 1:
Use the [UNION clause](https://en.wikipedia.org/wiki/Set_operations_(SQL)#UNION_operator), while selecting only one column that you want to aggregate in a **CASE** clause.
```sql
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_hc = 'Y' THEN 'HC' END  DOCUMENT FROM signatories_t 
UNION 
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_fcr = 'Y' THEN 'FCR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_whr = 'Y' THEN 'WHR' END DOCUMENT FROM 
 signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_weight_note = 'Y' THEN 'WEIGHT_NOTE' END DOCUMENT FROM signatories_t 
```
The above query will give you the below results:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2j1q64c1z6gmvlnx0qpa.png)

Step 2:
Create an anonymous view (**v_view**) and SELECT from it the columns that you want and agrregate the document column, by including a **GROUP BY** clause.
```sql
WITH v_view AS (
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_hc = 'Y' THEN 'HC' END  DOCUMENT FROM signatories_t 
UNION 
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_fcr = 'Y' THEN 'FCR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_whr = 'Y' THEN 'WHR' END DOCUMENT FROM signatories_t 
UNION
SELECT signatory_id
      ,insert_on 
      ,insert_by 
      ,last_updated_on 
      ,last_updated_by 
      ,signatory_user
      ,for_bu
      ,signatory_active
      ,CASE WHEN allow_weight_note = 'Y' THEN 'WEIGHT_NOTE' END DOCUMENT FROM signatories_t 

) SELECT 
      signatory_id
     ,insert_on 
     ,insert_by 
     ,last_updated_on 
     ,last_updated_by 
     ,signatory_user 
     ,for_bu
     ,signatory_active 
     ,listagg(document,'/') within group(order by 1) as document
FROM v_view
GROUP BY signatory_id, insert_on, insert_by, last_updated_on, last_updated_by,signatory_user, for_bu, signatory_active
 ;
```

![Result from above query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vtbvac1ojnufv6d01gf1.png)

**NOTE:** [LISTAGG](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466) is one of OracleSQL aggregate functions (return a single result row based on groups of rows, rather than on single rows).
I used [SQLDeveloper](https://docs.oracle.com/cd/E12151_01/index.htm) to access my schema table and run the queries.