SQL: Making a 'computation row'

I have a table that looks like this

TYPE | A | B | C | ... | Z
one  | 4 | 4 | 4 | ... | 4
two  | 3 | 2 | 2 | ... | 1

And I wanted to insert a row with a computation (row one minus row two):

TYPE | A | B | C | ... | Z
one  | 4 | 4 | 4 | ... | 4
two  | 3 | 2 | 2 | ... | 1
delta| 1 | 2 | 2 | ... | 3

I was thinking of a SQL command that looks like

(select A from table where type=one) - (select A from table where typ开发者_开发问答e=two)

Down side is, it's too long and I also have to do that for all the columns (A-Z) and that's quite a lot.

I'm sure there's a more elegant way of doing this.

PS:

The sequence of my code looks like this btw:

// I'm inserting the data from a RawTable to a TempTable

INSERT one
INSERT two
INSERT delta
INSERT three
INSERT four
INSERT delta
...
INSERT onehundredone
INSERT onehundredtwo
INSERT delta

I have added an ID column with identity to your temp table. You can use that to figure out what rows should be grouped.

create table YourTable
(
  ID int identity primary key,
  [TYPE] varchar(20),
  A int,
  B int,
  C int
)

insert into YourTable ([TYPE], A, B, C)
select 'one',   4, 4, 4 union all
select 'two',   3, 2, 2 union all
select 'three', 7, 4, 4 union all
select 'four',  3, 2, 2 union all
select 'five',  8, 4, 4 union all
select 'six',   3, 2, 2


select T.[TYPE], T.A, T.B, T.C
from
  (
    select
      T.ID,
      T.[TYPE],
      T.A,
      T.B,
      T.C
    from YourTable as T  
    union all  
    select
      T2.ID,
      'delta' as [TYPE],
      T1.A-T2.A as A,
      T1.B-T2.B as B,
      T1.C-T2.C as C
    from YourTable as T1
      inner join YourTable as T2
        on T1.ID = T2.ID-1 and
           T2.ID % 2 = 0
  ) as T
order by T.ID, case T.[TYPE] when 'delta' then 1 else 0 end

Result:

TYPE                 A           B           C
-------------------- ----------- ----------- -----------
one                  4           4           4
two                  3           2           2
delta                1           2           2
three                7           4           4
four                 3           2           2
delta                4           2           2
five                 8           4           4
six                  3           2           2
delta                5           2           2

Sorting on column C from first row in group:

select T.[TYPE], T.A, T.B, T.C
from
  (
    select
      T1.ID,
      T1.[TYPE],
      case T1.ID % 2 when 1 then T1.C else T2.C end as Sortorder,
      T1.A,
      T1.B,
      T1.C
    from YourTable as T1
      left outer join YourTable as T2
        on T1.ID = T2.ID+1  
    union all  
    select
      T2.ID,
      'delta' as [TYPE],
      T1.C as Sortorder,
      T1.A-T2.A as A,
      T1.B-T2.B as B,
      T1.C-T2.C as C
    from YourTable as T1
      inner join YourTable as T2
        on T1.ID = T2.ID-1 and
           T2.ID % 2 = 0
  ) as T
order by T.Sortorder, T.ID, case T.[TYPE] when 'delta' then 1 else 0 end

I'm not aware of any way to do this "easily" (i.e. without having to specify every column), I can't come up with any way to do it easily, so I'll go on the record as saying that it can't be done. Easily.

The non-easy way would be to build dynamic code--something that loops through the database metadata, builds a string containing the statement(s) to execute your desired routine column by column, and then execute that string. You really want to avoid this whenever possible.

One shortcut, if you just need to build a procedure or function that does this (i.e. build once run many), you could copy the list of columns into a spreadsheet (Excel), build out the highly-repetitive statements using forumlas that reference the column names, and then copying the results back. (This is much simpler to do than it is to explain.)

I have no idea why you're doing this, but the way I'd approach it is:

insert into table
select 'delta', 
t1.a - t2.a, 
t1.b - t2.b
.....
from table t1, 
table t2
where t1.type = 'one'
and t2.type = 'two'

You would have to run this query immediately after inserting "one" and "two", then re-run it after inserting "three" and "four". Nasty nasty nasty.

If you can re-name the columns in some way, or create a numerical column, you could run it in a single query.

When you replace one for 1, two for 2, and so on, then maybe this sql could work:

INSERT INTO PodMays
SELECT
  "Delta", A.A-B.A, A.B-B.B, A.C-B.C, A.D-B.D, A.E-B.E
FROM
  (
    SELECT TOP 1
      * 
    FROM
      (SELECT TOP 2 * FROM PodMays WHERE Type <> "Delta" ORDER BY Type DESC)
    ORDER BY
      Type ASC
  ) AS A,
  (
    SELECT TOP 1 
      * 
    FROM 
      (SELECT TOP 2 * FROM PodMays WHERE Type <> "Delta" ORDER BY Type DESC)
    ORDER BY 
      Type DESC
  ) AS B