如何去掉空值表一个左外部联接透视表

Question

我想在结果表中将这些NULL值设置为零。到目前为止，我找不到任何解决方案。

（A）

CREATE TABLE ##Temp1
 (
    tmp_id varchar(8),
    tmp_ac varchar(5),
    tmp_amt decimal(15,2)
 )
Record :
id   ac      amt
1    30100   100.00
2    30400   300.00
3    30500   500.00

（B） 创建动态列数据透视表：

declare @cols as varchar(max)
declare @query as varchar(max)
select @cols = ISNULL(@cols + ',','') + '[' + tmp_ac + ']' from ##Temp1 order by tmp_ac 
set @query = 'select tmp_id, '+ @cols + ' into ##TempPV from 
              (
                  select tmp_id, tmp_ac, isnull(tmp_amt,0) as tmp_amt
                  from ##Temp1
              ) x
              pivot
              (
                 sum(tmp_amt)
                  for tmp_ac in (' + @cols + ')
              ) p'
exec (@query)

（C） 左侧外部联接表A到表枢轴：

select
 ##TempA.f_id  as [ID],
##TempPV.*          
from ##TempA
left outer join  ##TempPV
on  ##TempA.f_id = tmp_id
order by f_id

（D） 结果：

Id   30100    30400    30500
1    100.00   NULL     NULL
2    NULL     300.00   NULL
3    NULL     NULL     500.00
4    NULL     NULL     NULL
5    NULL     NULL     NULL

（E） 预期结果：

Id   30100    30400    30500
1    100.00   0.00     0.00
2    0.00     300.00   0.00
3    0.00     0.00     500.00
4    0.00     0.00     0.00
5    0.00     0.00     0.00

Answer 1

您必须在主SELECT中使用coalize（或isull）。我还添加了一个带有tempA和5个id的LEFT JOIN。

查询：

declare @col_null as varchar(max)
declare @cols as varchar(max)
declare @query as varchar(max)
select @col_null = ISNULL(@col_null + ',','') + ' ['+tmp_ac+ '] = coalesce([' + tmp_ac + '], 0)' from #Temp1 order by tmp_ac 
select @cols = ISNULL(@cols + ',','') + ' [' + tmp_ac + ']' from #Temp1 order by tmp_ac 
set @query = 'select id, '+ @col_null + ' /*into #TempPV*/ from 
              (
                  select id, tmp_ac, isnull(tmp_amt,0) as tmp_amt
                  from #tempA ta
                  LEFT JOIN #Temp1 t1 ON ta.id = t1.tmp_id
              ) x
              pivot
              (
                 sum(tmp_amt)
                  for tmp_ac in (' + @cols + ')
              ) p'

您必须使用第二个列列表，因为数据透视只需要列名，并且SELECT必须如下所示：[30400] = coalesce([30400], 0)

输出：

id  30100   30400   30500
1   100.00  0.00    0.00
2   0.00    300.00  0.00
3   0.00    0.00    500.00
4   0.00    0.00    0.00
5   0.00    0.00    0.00

数据：

CREATE TABLE #Temp1
(
    tmp_id varchar(8),
    tmp_ac varchar(5),
    tmp_amt decimal(15,2) 
)
INSERT INTO #Temp1(tmp_id, tmp_ac, tmp_amt) values
    (1, 30100, 100.00)
    , (2, 30400, 300.00)
    , (3, 30500, 500.00)
CREATE TABLE #TempA(id varchar(8));
INSERT INTO #tempA(id) values (1), (2), (3), (4), (5);

Answer 2

不确定这是否有效，但我正在尝试构建查询（C），并通过ISNULL（）动态添加列名。。。

（C） 左侧外部联接表A到表枢轴：

declare @cols2 as varchar(max)
select @cols2 = ISNULL(@cols2 + ',','') + 'ISNULL(##TempPV.[' + tmp_ac + '], 0) as [' + tmp_ac + ']' from ##Temp1 order by tmp_ac 
set @query2 = 'select
               ##TempA.f_id  as [ID],
               ' + @cols2 + '          
               from ##TempA
               left outer join  ##TempPV
               on  ##TempA.f_id = tmp_id
               order by f_id'
exec (@query2)