How to shrink x number of columns into a finite number of columns

I have a set of survey results of which one question asks for a contact reason. There are 35 potential responses and the user can select a maximum of five. The results of the survey are stored in 35 columns with (1,null) denoting a positive/negative response.

How do I shrink the 35 columns results down into the 5 maximum responses (ContactReason1, ContactReason2…..)?

The best idea I’ve come up with so far is to unpivot the survey data into a results set with a unique_id and a contact_reason column, dropping any null values but im struggling to transform the “list” values back into the horizontal format.

Survey Results

Unique_ID     CR1     CR2     CR3    CR4     CR5 ......CR35

Unpivot Results

Unique_ID     ALLCRs

Desired Results

Unique_ID     CR1     CR2     CR3    CR4     CR5

Answer

Isn’t this something you could do in a calculated column pretty easily?

ref: https://www.sqlbook.com/sql-string-functions/sql-concatenate/

ref: https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver15

pseudocode:

ISNULL(CR1 + ',','')+ISNULL(CR2 + ',','')...

ISNULL checks to see if the column is null. If it is, create the element and add a comma. If not, null element.