CODEWITHSUNDEEP
sqlsql-servert-sql
Ammar Tareen
Ammar Tareen

Reputation: 51

T-sql using values from one table and displaying them in different columns

My database Table looks like:

ID | INDEX | Value |
1  |   0   |   3   |
1  |   1   |   5   |
1  |   2   |   7   |
2  |   0   |   4   |
2  |   1   |   6   |
2  |   2   |   2   |

What I want my output to look like is the difference of the values column based on their index i.e. value(id=2,index = i) - value(id = 1, index = i) so the output table will look like

INDEX | Delta Value |
  0   |     1       |
  1   |     1       |
  2   |    -5       |

My attempt at solving this problem is as follows:

SELECT Top 6
    col1.value column1,
    col2.value column2,
    col2.value - col1.value
FROM My_Table col1
INNER JOIN  My_Table col2
    ON col1.index = col2.index 
WHERE col1.id = 1 
    OR col2.id = 2

I know there are problems with this query. But I just haven't been able to produce the output that I want. Any help is appreciated.

Upvotes: 1

Views: 79

Answers (3)

th1rdey3
th1rdey3

Reputation: 4358

have a look at mine

 select t1.[index],
    t2.value-t1.value
 from my_table t1 inner join my_table t2
 on t1.[index] = t2.[index]
 where t1.id = 1 and t2.id = 2
 order by t1.[index]

Upvotes: 0

EricZ
EricZ

Reputation: 6205

Do you want something like this?

select
    col1.value column1,
    col2.value column2,
    col2.value - col1.value AS delta
From My_Table col1
INNER JOIN  My_Table col2 
    ON col1.index = col2.index
    AND col2.id = 2 
where col1.id = 1

Upvotes: 0

roman
roman

Reputation: 117485

You can do this by join

select
    t1.ind, t1.value - t2.value as delta
from My_Table as t1
    inner join My_Table as t2 on t2.id = 2 and t2.ind = t1.ind
where t1.id = 1

Or by simple aggregate:

select
    ind, sum(case id when 1 then 1 when 2 then -1 end * value) as delta
from My_Table
group by ind

Upvotes: 3

Related Questions