如何将SQL self连接到nulls

I am using MS SQL 2008. My table looks like this:

我正在使用MS SQL 2008。我的桌子是这样的:

| Name  | Code | Amt  |
| ----- | ---- | ---- |
| April |  A   | 1.23 |
| Barry |  A   | 2.34 |
| Barry |  B   | 3.45 |
| Cliff |  A   | 4.56 |
| Cliff |  B   | 5.67 |
| Cliff |  C   | 6.78 |

I need the output to be this:

我需要输出如下:

| Name  | Code_A | Code_B | Code_C |
| ----- | ------ | ------ | ------ |
| April |  1.23  |  NULL  |  NULL  |  
| Barry |  2.34  |  3.45  |  NULL  |
| Cliff |  4.56  |  5.67  |  6.78  |

The NULLs can be zero.

零可以是零。

With a self join I am able to get Cliff, but unable to get Barry and April because i'm using something like this which only outputs if all three conditions are available.

有了self join，我可以得到Cliff，但是不能得到Barry和April，因为我使用的是这样的东西，只有当这三个条件都具备时才会输出。

SELECT     a.Name, a.Amt Code_A, b.Amt Code_B, c.Amt Code_C
FROM       Table_1 as c INNER JOIN
                  Table_1 AS b ON c.Name = b.Name INNER JOIN
                  Table_1 AS a ON b.Name = a.Name 
WHERE     (a.Code = 'A') AND (b.Code = 'B') AND (c.Code = 'C')

2 个解决方案

#1

Instead of JOINs, I think a PIVOT is more appropriate here:

我认为一个轴心在这里更合适，而不是连接:

SELECT 
    Name, 
    [A] AS Code_A, 
    [B] AS Code_B, 
    [C] AS Code_C
FROM (
    SELECT Name, Code, Amount
    FROM Table_1
) t
PIVOT (
    SUM(Amount)
    FOR Code IN ([A], [B], [C])
) AS pvt

#2

A completely sql engine agnostic way is:

一个完全与sql引擎无关的方法是:

select names.Name, 
   (select sum(a2.Amt) from amounts a2
    where a2.Name = names.Name
       and a2.Code = 'A') as AmtA,
   (select sum(a3.Amt) from amounts a3
    where a3.Name = names.Name
       and a3.Code = 'B') as AmtB,
   (select sum(a4.Amt) from amounts a4
    where a4.Name = names.Name
       and Code = 'C') as AmtC
from (select distinct Name from amounts) as names

Where you select the unique set of names, and then sum up amounts for each particular code in place. This is more intended to be instructional as to how SQL works.

选择一组唯一的名称，然后对每个特定代码的金额进行汇总。这更倾向于指导SQL如何工作。

In practice, I wouldn't actually use this in your case -- PIVOT is going to be much more efficient for any engine that supports it. As shown here: http://sqlfiddle.com/#!3/7cb0a/5

在实践中，我不会在你的例子中使用这个轴对于任何支持它的引擎来说都要高效得多。如下所示:http://sqlfiddle.com/ ! 3/7cb0a / 5

#1