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
;
The query above retrives this result, that we are working with tonight.
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
The above query will give you the below results:
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
;
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)
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.
Thank you Aaron, please append link with another alternative.