Geek asks and answers

How to make SQL self-join with zeros

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

| 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 a conclusion:

| 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  |

NULLs can be null.

With self-join, I can get Cliff, but I can not get Barry and April, because I use something like this, which is only output if all three conditions are available.

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')
+3
source share
2 answers

Instead of JOINs, I think here PIVOT:

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
+4
source

The astronomical way of a fully sql engine:

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

Here you select a unique set of names, and then summarize the amounts for each specific code. This is more intended to teach how SQL works.

- PIVOT , . : http://sqlfiddle.com/#!3/7cb0a/5

+2

More articles:


All Articles