Menu
What I'm trying to do is provide a value if another value doesn't exists within my pivot table.
Pivot table
SELECT *
FROM MyTable
PIVOT ( MAX(Number) for Total in ([Bob], [Jim], [Carol], [Simon])) as MaxValue
Result
Item | Bob | Jim | Carol | Simon
Item1 3 4 7
Item2 2 9 1
Item3 5
What I'm trying to improve on the table above is to show if the person has been assigned an item if there is no number there.
Expected result
Item | Bob | Jim | Carol | Simon
Item1 3 4 X 7
Item2 2 9 1 X
Item3 X X X 5
I have a column (was commented out above) that has the person's name if the person was assigned that item but I was thinking maybe I could use COALESCE to place the "X" there if the user was assigned the item but nothing if not. Although I'm not able to find out how to do this. Perhaps this is the wrong approach. Let me know if I let out some information. Thanks!
712
To manage NULL values, the Coalesce in SQL and IsNull functions are used. The user-defined values replace the NULL values during the expression evaluation process. The Coalesce in SQL function evaluates the arguments in the specified order and always returns the first non-null value from the argument list.
You can have as many input values/columns to the COALESCE function as you like, but remember: order is important here since the first non-null value is the one that is returned.
COALESCE and ISNULL advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.
For your specific case I would say isnull is clearly faster. This is not to say it is better in any other given situation. Using straight up values, or nvarchars or bits instead of int, or a column that is not a primary key, or Nesting isnull versus adding parameters to coalesce could change things.
Yes, you can use COALESCE on the final select list to replace the null values with an X:
SELECT Item,
coalesce([Bob], 'X') Bob,
coalesce([Jim], 'X') Jim,
coalesce([Carol], 'X') Carol,
coalesce([Simon], 'X') Simon
FROM MyTable
PIVOT
(
MAX(Number)
for Total in ([Bob], [Jim], [Carol], [Simon])
) as MaxValue
Note, depending on the datatype of the Total column, you might have to cast/convert the value so you can replace the null with a string.
If you had to convert the datatype, the query will be:
SELECT Item,
coalesce(cast([Bob] as varchar(10)), 'X') Bob,
coalesce(cast([Jim] as varchar(10)), 'X') Jim,
coalesce(cast([Carol] as varchar(10)), 'X') Carol,
coalesce(cast([Simon] as varchar(10)), 'X') Simon
FROM MyTable
PIVOT
(
MAX(Number)
for Total in ([Bob], [Jim], [Carol], [Simon])
) as MaxValue
See SQL Fiddle with Demo. This returns:
| ITEM | BOB | JIM | CAROL | SIMON |
-------------------------------------
| item1 | 3 | 4 | X | 7 |
| item2 | 2 | 9 | 1 | X |
| item3 | X | X | X | 5 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
