Display a field's value, if null, move to the next

Question

I'm hoping someone can help me figure this out! I am using my returned data to fill in a form. There is one question on the form that can have multiple answers, Q8ReasonA-Q8ReasonH. My goal is to display each answer in sequence without space between them.

For example, if person answered question 8 by selecting B, F, and H, I want it to display:

Question eight? Answer: B, F, H

Right now it's displaying spaced out (leaving blank space where the A answer would be for example). Here's part of my code:

SELECT DISTINCT SFA.DOB, SFA.Name, SFA.SupvsrName,
   SFA.Question8,MFA.Q8ReasonA, MFA.Q8ReasonB, MFA.Q8ReasonC,
   MFA.Q8ReasonD, MFA.Q8ReasonE, MFA.Q8ReasonF, MFA.Q8ReasonG, MFA.Q8ReasonH
FROM SingleFileAnswer SFA
JOIN MultipleFileAnswer MFA on SFA.SupvsrID=MFA.SupvsrID
WHERE SFA.SupvsrID=1234

Where and how are you concatenating the different answers together? Your current script is returning several separate string values. — iamdave, Feb 23 '17 at 13:43
This just screams of a horrible design. You have violated 1NF here which is causing you pain. Your answers should be in rows, not columns. Consider how painful this will be if you want to add a new reason. You will have to change every single query. If you fix the design, the query will be simple. — Sean Lange, Feb 23 '17 at 14:05
I didn't include all of my SQL; these are just some of the values that are being returned from a different query where I converted the specific answer rows into their own columns. That query is dynamic and will flex as I need it to. These answers are structured list items the user chooses. The query is feeding the form the data to fill in. Since there is more than one user, each user can only have one row returned - hence moving all user answers to one row/many columns vs few columns/many rows. — Bester, Feb 23 '17 at 16:12

Gurwinder Singh · Answer 1 · 2017-02-23T13:54:50.887

You could use CASE with STUFF to produce comma separated values of only non-null answers:

select distinct SFA.DOB,
    SFA.name,
    SFA.SupvsrName,
    SFA.Question8,
    STUFF(
        case when MFA.Q8ReasonA is not null then ', A' else '' END +
        case when MFA.Q8ReasonB is not null then ', B' else '' END +
        case when MFA.Q8ReasonC is not null then ', C' else '' END +
        case when MFA.Q8ReasonD is not null then ', D' else '' END +
        case when MFA.Q8ReasonE is not null then ', E' else '' END +
        case when MFA.Q8ReasonF is not null then ', F' else '' END +
        case when MFA.Q8ReasonG is not null then ', G' else '' END +
        case when MFA.Q8ReasonH is not null then ', F' else '' END
    , 1, 2, '') answers 
from SingleFileAnswer SFA
join MultipleFileAnswer MFA on SFA.SupvsrID = MFA.SupvsrID
where SFA.SupvsrID = 1234

If you want to display the actual answer instead:

select distinct SFA.DOB,
    SFA.name,
    SFA.SupvsrName,
    SFA.Question8,
    STUFF(
        coalesce(', ' + MFA.Q8ReasonA, '') +
        coalesce(', ' + MFA.Q8ReasonB, '') +
        coalesce(', ' + MFA.Q8ReasonC, '') +
        coalesce(', ' + MFA.Q8ReasonD, '') +
        coalesce(', ' + MFA.Q8ReasonE, '') +
        coalesce(', ' + MFA.Q8ReasonF, '') +
        coalesce(', ' + MFA.Q8ReasonG, '') +
        coalesce(', ' + MFA.Q8ReasonH, '') 
    , 1, 2, '') answers 
from SingleFileAnswer SFA
join MultipleFileAnswer MFA on SFA.SupvsrID = MFA.SupvsrID
where SFA.SupvsrID = 1234

This falls over if the values of the various `Reason` columns are different to the values `A-H` — iamdave, Feb 23 '17 at 13:50
This also works. Is there a way to do a CR instead of a comma? So that it would display each answer on a new line instead of in a list. — Bester, Feb 23 '17 at 16:13
I think this kind of processing is better handled in your application. You could use char(), but I don't really a fan of this approach and wouldn't suggest it. — Gurwinder Singh, Feb 23 '17 at 16:19

score 0 · Answer 2 · answered Feb 23 '17 at 13:47

0

You can concatenate strings together with the concat function, and check for null values with the isnull function. Using stuff removes the initial comma and space (Starts at character 1, and replaces 2 characters with and empty string ''):

select distinct
       SFA.DOB
      ,SFA.Name
      ,SFA.SupvsrName
      ,SFA.Question8
      ,MFA.Q8ReasonA
      ,MFA.Q8ReasonB
      ,MFA.Q8ReasonC
      ,MFA.Q8ReasonD
      ,MFA.Q8ReasonE
      ,MFA.Q8ReasonF
      ,MFA.Q8ReasonG
      ,MFA.Q8ReasonH
      ,stuff(concat(isnull(', ' + MFA.Q8ReasonA,'')
                    ,isnull(', ' + MFA.Q8ReasonB,'')
                    ,isnull(', ' + MFA.Q8ReasonC,'')
                    ,isnull(', ' + MFA.Q8ReasonD,'')
                    ,isnull(', ' + MFA.Q8ReasonE,'')
                    ,isnull(', ' + MFA.Q8ReasonF,'')
                    ,isnull(', ' + MFA.Q8ReasonG,'')
                    ,isnull(', ' + MFA.Q8ReasonH,'')
                    )
            ,1,2,'') as Concatenated
from SingleFileAnswer as SFA
    join MultipleFileAnswer as MFA
        on SFA.SupvsrID = MFA.SupvsrID
where SFA.SupvsrID = 1234;

answered Feb 23 '17 at 13:47

iamdave

12,023
3
24
53

Thank you! This works. Is there a way to do a CR instead of a comma? So that it would display each answer on a new line instead of in a list? – Bester Feb 23 '17 at 16:06
@Bester Per the details in [this answer](http://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string) you should use either `char(10)` or `char(10)+char(13)` depending on where you are displaying the data. For the benefit of other people with the same question, please mark this as the answer if it is what you were looking for. – iamdave Feb 23 '17 at 16:15
I had already viewed that q&a and tried using the char() within the framework above, but it didn't put returns in. That's why I asked here. – Bester Feb 23 '17 at 16:26
@Bester Where are you checking to see if the carriage returns are being included? The Output window in SSMS ignores line breaks and carriage returns and I know for a fact that this works as I have successfully output data to SSRS and excel in this manner that did include the breaks. – iamdave Feb 23 '17 at 16:36
In the form that the query is feeding. As written above, it displays in my form as: "Text for reason A., Text for reason B.,,,,Text for reason F." When I add in the CHAR() it displays the same way. – Bester Feb 23 '17 at 16:42
@Bester Then your data is not `null` it is just blank. These are crucially different things. You will need to add a check for an empty string (ie: `''`) in your code and then do the `concat`. – iamdave Feb 23 '17 at 16:56

Display a field's value, if null, move to the next

2 Answers2