Attempting to Pivot a column of grades and coming up with NULL values

Question

I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything.

Here's the source data:

    Catalog Class Nbr   ID  Status  Grade
    281 82142   5000293644  Enrolled    1
    295 12128   1012642448  Enrolled    1
    296 12124   1012642448  Enrolled    4
    280 82141   5000289133  Enrolled    4
    295 10528   1012923963  Enrolled    1

And the Pivot Query:

      SELECT *
      FROM (
      SELECT [ID], [Catalog], [Grade]
      From CSNCombined.dbo.csnName
      ) as c
      PIVOT
      (
        SUM([Grade])
        FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
        )
        AS PVT

The query executes fine, but all values are NULL.

Answer 1

Change your query to this:

SELECT 
     pvt.ID
    ,[198] = isnull([198],0)
    ,[281] = isnull([281],0)
    ,[280] = isnull([280],0)
    ,[295] = isnull([295],0)
    ,[296] = isnull([296],0)
    ,[297] = isnull([297],0)
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

or perhaps this:

SELECT 
     ID = cast(pvt.ID as char(12))
    ,[198] = isnull(str([198]),'        -')
    ,[281] = isnull(str([281]),'        -')
    ,[280] = isnull(str([280]),'        -')
    ,[295] = isnull(str([295]),'        -')
    ,[296] = isnull(str([296]),'        -')
    ,[297] = isnull(str([297]),'        -')
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

to yield:

ID           198        281        280        295        296        297
------------ ---------- ---------- ---------- ---------- ---------- ----------
1012642448           -          -          -           1          4         -
1012923963           -          -          -           1         -          -
5000289133           -          -           4         -          -          -

The SQL value null represents the absence of data, so when you pivot and a cell as no data a null will perforce be generated as the cell value. It is your responsibility as the programmer to coalesce the null to whatever domain value is appropriate for missing data, when that makes sense.