显示所有带有SQL Pivot的行，包括记录计数为零的行

问题描述：

是否可以使用Pivot包含不存在记录的行，并在结果中显示0或null?

Is there a way using Pivot to include rows for which no records exist and show 0s or nulls for the results?

我希望查询结果看起来像这样:-

I want the reults of the query to look something like this :-

     A    B   C    D
5   12   81   107  0
4   0     0    0   0 
3   1    12   12   5 
2   3    0     0   0 
1   0    0     0   0

但是，当前Pivot不会返回空行，而我的结果如下所示:-

However, currently Pivot is not returning empty rows and my results look like this :-

     A    B   C    D
5   12   81   107  0
3   1    12   12   5 
2   3    0     0   0

可以通过在数据透视表上执行某种左外部联接"以显示所有行来实现此目的的任何方式?

Any way that this can be achieved with by doing a sort of 'left outer join' on the Pivot to show all rows?

答

您确实需要使用叉积和左连接来制造丢失的记录，以使它们出现

You really need to manufacture the missing records using a cross product and a left join in order to have them appear

declare @table table
(
      n     int not null,
      ch    char(1) not null,
      cnt   int not null,     

      primary key (n, ch)
)

insert into @table values (5, 'A', 12)
insert into @table values (5, 'B', 81)
insert into @table values (5, 'C', 107)
insert into @table values (3, 'A', 1)
insert into @table values (3, 'B', 12)
insert into @table values (3, 'C', 12)
insert into @table values (3, 'D', 5)
insert into @table values (2, 'A', 3)

declare @numbers table (n int not null primary key)
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4)
insert into @numbers values (5)

declare @chars table (ch char(1) not null primary key)
insert into @chars values ('A')
insert into @chars values ('B')
insert into @chars values ('C')
insert into @chars values ('D')


select n, [A], [B], [C], [D]
  from 
  ( -- manufacture missing records 
    select n.n, ch.ch, coalesce(t.cnt, 0) as cnt
      from @numbers n
        cross join @chars ch 
        left join @table t on (n.n = t.n and ch.ch = t.ch)
  ) as t
pivot
(
  sum(cnt)
  for ch in ([A], [B], [C], [D])
) as pivotTable
order by n desc

显示所有带有SQL Pivot的行，包括记录计数为零的行

相关推荐