2

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.

7
  • 1
    Are the values being returned in your subquery for Catalog the same values being used as the new column names here -- FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])?
    – Taryn
    Sep 23, 2014 at 20:09
  • Yes those are all the catalog numbers. Sep 23, 2014 at 20:31
  • 1
    You'll get null if the catalog doesn't exist in the subquery - but your query works -- see this demo -- sqlfiddle.com/#!3/2ea0a/2 If this still isn't right, then you'll need to edit your post with some more sample data, and then explain why your query isn't correct.
    – Taryn
    Sep 23, 2014 at 20:34
  • Is Grade nullable? If so, does this return any rows: SELECT * FROM csnName WHERE Grade IS NULL AND Catalog IN (198,281,...);? Also, please clarify what "all values" means - every single column, or just those coming from the pivot? Sep 23, 2014 at 20:39
  • Got it figured out. It had to do with the type of data for the column. Thanks! Sep 23, 2014 at 21:01

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.