Comma delimited list of row values

Question

I currently have a database structured similar to this:

Name | v_BirthDate | v_Drivers_License | v_SSN 
-----------------------------------------------
Anna |      1      |         0         |   1
Bob  |      0      |         1         |   0
Cal  |      1      |         1         |   1 
Dave |      0      |         0         |   1

The basic idea of the database is to see how the person was verified in a system; whether the person was verified in the system using their birth date, driver's license number, or their social.

What I would like to do is to take the row for each person and create another column that shows a list of the used verification methods in a comma-separated format.

Something similar to this:

Name | v_BirthDate | v_Drivers_License | v_SSN |    list 
-----------------------------------------------------------
Anna |      1      |         0         |   1   | BirthDate,SSN
Bob  |      0      |         1         |   0   | Drivers_License
Cal  |      1      |         1         |   1   | BirthDate,Drivers_License,SSN
Dave |      0      |         0         |   1   | SSN

I used the WITH clause to create a temporary table that changes the 1s to the string variable such as BirthDate, Drivers_License, and SSN. However, I am not sure how I am able to create the list column. Thank you in advance.

Answer 1

You can do this with a giant case and some string logic:

select t.*,
       trim(leading ',' from
            (case when v_BirthDate = 1 then ',BirthDate' else '' end) ||
            (case when v_Drivers_License = 1 then ',Drivers_License' else '' end) ||
            (case when v_SSN = 1 then ',v_SSN' else '' end)
           ) as list
from t;

This creates a string composed of each component preceded by a comma. The trim() removes the initial comma.