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

Solution 1:

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.

Solution 2:

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'

Solution 3:

This is a perfect query for the PIVOT operator.

SELECTSID,
  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
) SPIVOT (
  MIN(Present)
  FOR FormID IN ([Form1],[Form2],[Form3],[Form4],[Form5],[Form6])
) ASPORDERBYSID;