sql - 如何在空白值上使用 PIVOT 子句

Question

示例数据:

Data table1:
prodid  type location(there are more columns, just ignoring them for this example)
p001        t1      l1
p002        t1      l2
p003        t3      l1
p004        t2      
p005        t1      l1

Need a summary like
type    Blank [l1]  [l2]
t1      0           2           1
t2      1           0           0
t3      0           1           0

我面临的问题是位置字段中的空白值。我不知道如何在数据透视查询中表示空白位置值。

Pivot query:
1: select type, [] as Blank, [l1], [l2], Blank + [l1] + [l2] as Total from
2: (select type, location from table1)
3: pivot 
4: (count(location) for location in ([],[l1],[l2]) t2

Error on line 1 & 4
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. 

Answer 1

将 null/空白位置换成虚拟值怎么样？所以改变

select type, location from table1

到

select type, CASE WHEN location is null THEN 'ZZ' ELSE location END as location from table1

然后该列将是 zz 或您选择的任何虚拟值