CODEWITHSUNDEEP
sql-serverpivot
David Shochet
David Shochet

Reputation: 5375

ISNULL with PIVOT in SQL

With pivot, I need to replace all null values and 0 values with a dash. I started with getting rid of null values:

  SELECT FunctionCode, ISNULL(Col1, '-') AS Col1, ISNULL(Col2, '-') AS Col2
  FROM
  (
    SELECT Company, FunctionCode, [Count]
    FROM MyTable
  ) AS D
  PIVOT
  (
    SUM([Count])
    FOR Company IN (Col1, Col2)
  ) PIV;

For some reason, NULL is replaced with 0 instead of '-'. What can I try next?

Upvotes: 0

Views: 2477

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

SUM() can only operate on the numeric data types.

The numeric data types have a higher precedence than any textual data type1.

So the - is converted to a number, e.g.:

select CONVERT(int,'-')

Produces the result 0.

So, the solution is to convert your values to a textual type.

SELECT FunctionCode, ISNULL(CONVERT(varchar(10),Col1), '-') AS Col1,
                     ISNULL(CONVERT(varchar(10),Col2), '-') AS Col2
...

And with 0s dealt with also:

SELECT FunctionCode, ISNULL(CONVERT(varchar(10),NULLIF(Col1,0)), '-') AS Col1,
                     ISNULL(CONVERT(varchar(10),NULLIF(Col2,0)), '-') AS Col2
...

1This doesn't, technically, matter here because ISNULL doesn't follow the type rules - it always tries to convert its second argument to the same type as the first. But since the problem is the type of the first argument, the same fix applies.

Precedence would have mattered were we to be using COALESCE instead of ISNULL, which is usually preferred.

Upvotes: 2

Darka
Darka

Reputation: 2768

Here is example:

When INT type column set dash, then it becomes 0:

SELECT ISNULL( CAST(NULL AS INT), '-' )

But if we CAST it to some VARCHAR then it is normal dash :

SELECT ISNULL( CAST( CAST(NULL AS INT) AS VARCHAR(100)), '-' )

Upvotes: 1

Related Questions