How to get multiple values into one column using case statement?

user_6036670 0
Viewed 126

I am trying to add data from this table to an existing query:

enter image description here

I want to create a column called 'Assertions' and want to only pull in the column names where the value is 'Y'. For example for Control_ID 13009 (first line), the new Assertions column would show:

Control_ID    Assertions
13009        'Rights_Obligation, Valuation_Allocation, Existence_Occurence'

I tried using a case when statement like the one below:

case when ca.PRESENTATION_DISCLOSURE = 'Y' then 'Presentation_Disclosure'
when ca.RIGHTS_OBLIGATION = 'Y' then 'Rights_Obligation'
when ca.VALUATION_ALLOCATION = 'Y' then 'Valuation_Allocation'
when ca.COMPLETENESS = 'Y' then 'Completeness'
when ca.EXISTENCE_OCCURRENCE = 'Y' then 'Existence_Occurence'
end as Assertions

This query doesn't work because it's just putting the first assertion with the 'Y' value into the column. How do I modify the query so that it includes all Assertions where the value is 'Y'?

sql-server sql
2 Answers

You can use the CONCAT as follows:

TRIM( ',' FROM 
    CONCAT(CASE when ca.PRESENTATION_DISCLOSURE = 'Y' then 'Presentation_Disclosure' END,
           CASE when ca.RIGHTS_OBLIGATION = 'Y' then ',Rights_Obligation' END,
           CASE when ca.VALUATION_ALLOCATION = 'Y' then ',Valuation_Allocation' END,
           CASE when ca.COMPLETENESS = 'Y' then ',Completeness' END,
           CASE when ca.EXISTENCE_OCCURRENCE = 'Y' then ',Existence_Occurence' END
          )
     ) as Assertions

On 2014, You can use the following query as TRIM is not available in 2014.

Stuff( 
    CONCAT(CASE when ca.PRESENTATION_DISCLOSURE = 'Y' then 'Presentation_Disclosure' END,
           CASE when ca.RIGHTS_OBLIGATION = 'Y' then ',Rights_Obligation' END,
           CASE when ca.VALUATION_ALLOCATION = 'Y' then ',Valuation_Allocation' END,
           CASE when ca.COMPLETENESS = 'Y' then ',Completeness' END,
           CASE when ca.EXISTENCE_OCCURRENCE = 'Y' then ',Existence_Occurence' END
          )
     1, 1, '') as Assertions
user_115656290

Starting from SQL Server 2017, you may use CONCAT_WS():

CONCAT_WS (
   ', ',
   CASE when ca.PRESENTATION_DISCLOSURE = 'Y' then 'Presentation_Disclosure' END,
   CASE when ca.RIGHTS_OBLIGATION = 'Y' then 'Rights_Obligation' END,
   CASE when ca.VALUATION_ALLOCATION = 'Y' then 'Valuation_Allocation' END,
   CASE when ca.COMPLETENESS = 'Y' then 'Completeness' END,
   CASE when ca.EXISTENCE_OCCURRENCE = 'Y' then 'Existence_Occurence' END
) AS Assertions

For earlier versions CONCAT() is an option:

STUFF(
   CONCAT (
      CASE when ca.PRESENTATION_DISCLOSURE = 'Y' then ', Presentation_Disclosure' END,
      CASE when ca.RIGHTS_OBLIGATION = 'Y' then ', Rights_Obligation' END,
      CASE when ca.VALUATION_ALLOCATION = 'Y' then ', Valuation_Allocation' END,
      CASE when ca.COMPLETENESS = 'Y' then ', Completeness' END,
      CASE when ca.EXISTENCE_OCCURRENCE = 'Y' then ', Existence_Occurence' END
   ), 1, 2, ''
) AS Assertions
user_65780800
Related