SQL Server含NULL行的CASE用法及查询结果格式整理求助

Hey there! Welcome to Stack Overflow—so glad you’re here to ask your first question! No stress if things feel a bit unpolished right now, we’re all here to help you sort this out.

Your Current Output #

personno personcat answervale personno personcat answervale
-----------------------------------------------------------
12       1        yes        null      null      null
12       2        no
12       1        test       null      null      null
12       2        check

The Format You Want #

personcat personno answervale personcat personno answervale
-----------------------------------------------------------
12        1        yes        12        2        no
12        1        test       12        2        check

Key Fixes to Try #

Since you can’t share your full query, here are some targeted strategies to get your results aligned correctly:

• Fix unintended joins: The duplicate columns and null rows are likely coming from a self-join or cross join that isn’t filtered properly. Double-check your JOIN clauses to make sure you’re matching records on the right keys (like personno) and filtering out mismatched pairs.
• Pair matching records explicitly: If you want to group personcat 1 and personcat 2 entries for the same personno on a single line, use a targeted join to pair them directly. Here’s a simplified example:

  SELECT
      t1.personcat, t1.personno, t1.answervale,
      t2.personcat, t2.personno, t2.answervale
  FROM your_table t1
  JOIN your_table t2
    ON t1.personno = t2.personno
    AND t1.personcat = 1
    AND t2.personcat = 2
  -- Add filters if you need to exclude specific rows
  WHERE t1.answervale IS NOT NULL AND t2.answervale IS NOT NULL;
  

• Clean up incomplete rows: Your current output has lines missing half the data (like the second row only has three values). Make sure your query isn’t excluding columns accidentally or applying filters that cut off valid data.

内容的提问来源于stack exchange，提问作者mandy