Replacing NULL from results of CASE query

问题:

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' ?

回答1:

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;

回答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.

回答3:

 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'