在T-SQL语句中合并结果

I have to queries:

我要查询:

select id, name, num from pending  

id, name, num
1, first, 1  
3, third, 12

select id, name, num from completed

id, name, num
1, first, 100
2, second, 20

I want output to combine these, maybe like a pivot table, using T-SQL.

我希望输出结合这些,可能像一个数据透视表,使用T-SQL。


id, name, pending, completed, total
1, first, 1, 100, 101
2, second, null, 20, 20
3, third, 12, null, 12

How can this be written?

怎么写这个?

1 个解决方案

#1

No need for a pivot.

不需要枢轴。

SELECT
    COALESCE(p.id, c.id),
    COALESCE(p.name, c.name),
    p.num AS pending,
    c.num AS completed,
    COALESCE (p.num, 0) + COALESCE (c.num, 0) AS total
FROM
    pending p
    FULL OUTER JOIN
    completed c ON p.id = c.id

COALESCE is ANSI SQL but you could use ISNULL too

COALESCE是ANSI SQL,但您也可以使用ISNULL

#1