Replacing NULL from results of CASE query

Question

I have the following code which generates data similar to mine. The posting here (PivotWithoutAggregateFunction) suggested that using a CASE statement rather than PIVOT was better for non-numeric values. Which if this is not true then I guess now is the time to fix it !

      DECLARE @QA1 TABLE (SID varchar(7), FormID varchar(max), DateExam date, Present varchar(3))
      INSERT INTO @QA1 VALUES(1, 'Form1', '20110101', 'Yes')
      INSERT INTO @QA1 VALUES(2, 'Form1', '20110201', 'Yes')
      INSERT INTO @QA1 VALUES(3, 'Form1', '20110301', 'Yes')
      INSERT INTO @QA1 VALUES(4, 'Form1', '20110401', 'Yes')
      INSERT INTO @QA1 VALUES(5, 'Form1', '20110122', 'Yes')
      INSERT INTO @QA1 VALUES(1, 'Form2', '20110222', 'Yes')
      INSERT INTO @QA1 VALUES(2, 'Form2', '20110322', 'Yes')
      INSERT INTO @QA1 VALUES(3, 'Form2', '20110422', 'Yes')
      INSERT INTO @QA1 VALUES(1, 'Form3', '20110128', 'Yes')
      INSERT INTO @QA1 VALUES(1, 'Form4', '20110228', 'Yes')
      INSERT INTO @QA1 VALUES(5, 'Form5', '20110328', 'Yes')
      INSERT INTO @QA1 VALUES(5, 'Form6', '20111228', 'Yes')
      INSERT INTO @QA1 VALUES(4, 'Form2', '20111228', 'Yes')

    SELECT SID,
    MIN(CASE FormID WHEN 'Form1' THEN Present END) AS 'First',
    MIN(CASE FormID WHEN 'Form2' THEN Present END) AS 'Second',
    MIN(CASE FormID WHEN 'Form3' THEN Present END) AS 'Third',
    MIN(CASE FormID WHEN 'Form4' THEN Present END) AS 'Fourth',
    MIN(CASE FormID WHEN 'Form5' THEN Present END) AS 'Fifth',
    MIN(CASE FormID WHEN 'Form6' THEN Present END) AS 'Sixth'

    FROM @QA1

    GROUP BY SID
    ORDER BY SID

However, the output has NULL in Row/Columns which do not have any forms which while correct is much harder for me to read.

      SID    First  Second   Third  Fourth   Fifth   Sixth
       1      Yes    Yes      Yes     Yes     NULL    NULL
       2      Yes    Yes      NULL    NULL    NULL    NULL
       3      Yes    Yes      NULL    NULL    NULL    NULL
       4      Yes    Yes      NULL    NULL    NULL    NULL
       5      Yes    NULL     NULL    NULL    Yes     Yes

How can I alter my CASE so I either get nothing (ie '') or at least 'No' ?

score 2 · Answer 1 · answered Sep 05 '11 at 05:16

2

You should be able to wrap COALESCE around the offending MINs, for example:

COALESCE(MIN(CASE FormID WHEN 'Form1' THEN Present END), 'No') AS 'First',

I'm not certain how happy SQL Server would be with that but that's pretty standard SQL.

Apply the NULL adjustment after the MIN is probably a better call than trying to choose a safe value to put inside the MIN.

answered Sep 05 '11 at 05:16

mu is too short

426,620
70
833
800

That did it ! Thanks I did not know COALESCE – user918967 Sep 05 '11 at 05:19
@user918967: Everyone should know about COALESCE, it is a wonderful and useful variadic function (i.e. you can call it with as many arguments as needed). You could use `''` if you don't like `'No'`. – mu is too short Sep 05 '11 at 05:23

score 1 · Answer 2 · answered Sep 05 '11 at 05:19

1

 SELECT SID,
    isnull(MIN(CASE FormID WHEN 'Form1' THEN Present END),'') AS 'First',
    isnull(MIN(CASE FormID WHEN 'Form2' THEN Present END),'') AS 'Second',
    isnull(MIN(CASE FormID WHEN 'Form3' THEN Present END),'') AS 'Third',
    isnull(MIN(CASE FormID WHEN 'Form4' THEN Present END),'') AS 'Fourth',
    isnull(MIN(CASE FormID WHEN 'Form5' THEN Present END),'') AS 'Fifth',
    isnull(MIN(CASE FormID WHEN 'Form6' THEN Present END),'') AS 'Sixth'

or

  SELECT SID,
    isnull(MIN(CASE FormID WHEN 'Form1' THEN Present END),'No') AS 'First',
    isnull(MIN(CASE FormID WHEN 'Form2' THEN Present END),'No') AS 'Second',
    isnull(MIN(CASE FormID WHEN 'Form3' THEN Present END),'No') AS 'Third',
    isnull(MIN(CASE FormID WHEN 'Form4' THEN Present END),'No') AS 'Fourth',
    isnull(MIN(CASE FormID WHEN 'Form5' THEN Present END),'No') AS 'Fifth',
    isnull(MIN(CASE FormID WHEN 'Form6' THEN Present END),'No') AS 'Sixth'

answered Sep 05 '11 at 05:19

Icarus

63,293
14
100
115

When I use this syntax on the DateExam field, if there is no form, the date goes to 1900-01-01. Can I change that to '' ? – user918967 Sep 05 '11 at 05:26
@user918967 You can, but then you need to convert the DateExam field to varchar or I don't think SQL will like it; in other words this: isnull(CONVERT(varchar(10),DateExam,101),'') – Icarus Sep 05 '11 at 05:32
Thanks again ! One last bit. I added 34 days to DateExam and called it DateThirtyFuture so I can see if my form is overdue. So what I'd like to do is when the form is present, keep the Yes in the field, but if it is blank, check to see if it is over 34 days (DateThirtyFuture) and if so, put 'Over Due', if not, leave it blank ? – user918967 Sep 05 '11 at 06:05

score 0 · Accepted Answer · edited Oct 24 '11 at 18:37

This is a perfect query for the PIVOT operator.

SELECT
  SID,
  COALESCE([Form1],'No') AS [First],
  COALESCE([Form2],'No') AS [Second],
  COALESCE([Form3],'No') AS [Third],
  COALESCE([Form4],'No') AS [Fourth],
  COALESCE([Form5],'No') AS [Fifth],
  COALESCE([Form6],'No') AS [Sixth]
FROM (
  SELECT SID, FormID, Present FROM @QA1
) S
PIVOT (
  MIN(Present)
  FOR FormID IN ([Form1],[Form2],[Form3],[Form4],[Form5],[Form6])
) AS P
ORDER BY SID;

Replacing NULL from results of CASE query

3 Answers3

Linked