DEV Community

Cover image for SQL for Data Engineers (oracle SQL)
NAGERI
NAGERI

Posted on

SQL for Data Engineers (oracle SQL)

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.
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
      ;
Enter fullscreen mode Exit fullscreen mode

The query above retrives this result, that we are working with tonight.

Image description

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, while selecting only one column that you want to aggregate in a CASE clause.

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 
Enter fullscreen mode Exit fullscreen mode

The above query will give you the below results:

Image description

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.

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
 ;
Enter fullscreen mode Exit fullscreen mode

Result from above query

NOTE: LISTAGG is one of OracleSQL aggregate functions (return a single result row based on groups of rows, rather than on single rows).
I used SQLDeveloper to access my schema table and run the queries.

Top comments (2)

Collapse
 
aarone4 profile image
Aaron Reese

2 observations:
1) in MSSQL there is no LISTAGG function, to achieve the same result you would need to use STUFF...FOR XML
2 ) The solution feels clumsy with all the unions. I don't know PL/SQL but there should be an UNPIVOT function that effectively groups by all the common fields and then adds the document fields as additional columns. If you do this in a CTE (anonymous view?) You can then just concatenate the new document columns. Probably easier to maintain as new document types are added.

Collapse
 
nageri profile image
NAGERI

Thank you Aaron, please append link with another alternative.