SQL Server: how can I use COALESCE with a PIVOT table?

Question

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!

score 7 · Accepted Answer · answered Apr 03 '13 at 14:17

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 |

SQL Server: how can I use COALESCE with a PIVOT table?

1 Answers1