Fill Variable with Case Statement Results

Question

I have a questionnaire that my users have filled out (several thousand a day)

The result is each questionnaire record contains 70 something fields (that correspond to each question)

I've been asked to identify all the affirmatives for each of the 70 questions and concatentate them into one field (a summary of all the issues identified for that record).

In other languages (VBA in particular) I would accomlish this by initializing a variable to '', looping through my recordset and setting the variable to what it was previously + the field name of the issue. I'm not sure how to accomplish this in sql.

I've tried...

DECLARE @strFYI AS NVARCHAR
SET @strFYI = ''

SELECT
    a.record_num
    ,CASE
         WHEN a.Date_Missing = 'Yes' THEN @strFYI = @strFYI + 'Date_Missing, '
         WHEN a.Unclear_Images = 'Yes' THEN @strFYI = @strFYI + 'Unclear_Images, '
         WHEN a.Damage = 'Yes' THEN @strFYI = @strFYI + 'Damage, '
         ELSE @strFYI
    END AS FYI_Reasons
FROM
    questionaretable a

But obviously that doesn't work. I'll also need to trim the last comma and space off the list once it's generated, but that shouldn't be a problem... I'm just not sure how to iterate through my records and build this concatenation in tsql :) I'm not even sure (because the syntax is wrong) if the variable would be reset to '' before each record was evaluated!

Can anyone help me out here?

Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Juan Carlos Oropeza, Oct 30 '15 at 21:07
That seems to be for concatenating rows of data, not columns of data :( — JakPackage, Nov 02 '15 at 13:55
Yes JakPackage, that is why I make a different answer for you — Juan Carlos Oropeza, Nov 02 '15 at 13:59

Juan Carlos Oropeza · Accepted Answer · 2015-10-31T03:03:09.620

0

This will be very ugly for 70 columns.

SELECT record_num, LEFT(strFYI, LEN(strFYI) - 2) 
FROM (
    SELECT
        a.record_num,
        (CASE WHEN a.Date_Missing = 'Yes' THEN 'Date_Missing, ' ELSE '' END) + 
        (CASE WHEN a.Unclear_Images = 'Yes' THEN 'Unclear_Images, ' ELSE  '' END) + 
        (CASE WHEN a.Damage = 'Yes' THEN 'Damage, ' ELSE '' END)  as strFYI
    FROM
        questionaretable a
     ) T

Maybe is cleaner using IIF

IIF ( boolean_expression, true_value, false_value )

SELECT record_num, LEFT(strFYI, LEN(strFYI) - 2) 
FROM (
    SELECT
        a.record_num,
        IIF(a.Date_Missing = 'Yes', 'Date_Missing, ' , '' ) + 
        IIF(a.Unclear_Images = 'Yes', 'Unclear_Images, ',  '') + 
        IIF(a.Damage = 'Yes', 'Damage, ', '')  as strFYI
    FROM
        questionaretable a
     ) T

As CElliot mention not IIF in 2008 so another solution may be

isnull((select 'Date_Missing, ' where a.Date_Missing = 'Yes'),'')

edited Oct 31 '15 at 03:03

answered Oct 30 '15 at 21:26

Juan Carlos Oropeza

47,252
12
78
118

IIF() was introduced with SQL Server 2012. This question is tagged SQL Server 2008R2. – CElliott Oct 31 '15 at 01:20
@CElliott Thanks for your comment, please check my update :) – Juan Carlos Oropeza Oct 31 '15 at 03:04
This idea worked for me :) For some reason, it's only LEN - 1 though... it doesnt seem to store the extra space after the comma for some reason (this only occurs at the end though, the space works just fine as a delimiter between the list items!) – JakPackage Nov 02 '15 at 14:15
@JakPackage That sound weird. Use a select to debug what is the value something like `SELECT '(' + strFYI + ')', LEN(strFYI)` to see the spaces and size in the final result – Juan Carlos Oropeza Nov 02 '15 at 14:18

Fill Variable with Case Statement Results

1 Answers1