从CASE查询结果中替换NULL [英] Replacing NULL from results of CASE query

问题描述

我有以下代码生成与我的数据相似的数据.此处的帖子( PivotWithoutAggregateFunction )建议使用CASE语句而不是PIVOT更好非数字值.如果这不是真的，那我现在该是修复它的时候了！

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

但是，输出在行/列中为NULL，它们没有任何形式，而正确的形式对我来说更难阅读.

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

如何更改我的CASE，使我什么也没有(即")或至少是"No"?

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

推荐答案

对于PIVOT运算符而言，这是一个完美的查询.

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;

这篇关于从CASE查询结果中替换NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！