CODEWITHSUNDEEP
sqlsql-serverleft-joinsql-null
Dave
Dave

Reputation: 166

How to combine two columns data of same table?

Consider that I have a stored procedure resulting in following result.

Stored Procedure Code

DECALRE @myTab TABLE(
  Id int NOT NULL,
  Name nvarchar(30) NOT NULL,
  Scale1 int,
  Scale2 int
)

Insert into @myData(Id,Name,Scale1,Scale2)
Select 
I.Id,
I.Name,
S1.Amount,
S2.Amount
From Information I 
Left Join Scale1 S1 on I.Scale1Id = S1.Id
Left Join Scale2 S2 on I.Scale2Id = S2.Id


Select * From @myTab

Here is Resulting Table.

+------+----------+--------+---------+
| ID   | NAME     | Scale1 | Scale2  |
+------+----------+--------+---------+
|    1 | Ramesh   |   NULL | 1230    |
|    2 | Khilan   |   1560 | NULL    |
|    3 | kaushik  |   3000 | NULL    |
|    3 | kaushik  |   1500 | NULL    |
|    4 | Chaitali |   2060 | NULL    |
|    5 | Hardik   |   NULL | 1569    |
|    6 | Komal    |   NULL | 2548    |
|    7 | Muffy    |   NULL | 1256    |
+------+----------+--------+---------+

Now I want to combine column Scale1 and Scale 2 and make it single column Scale, that has no null Data, like following example.

+------+----------+--------+
| ID   | NAME     | Scale  |  
+------+----------+--------+ 
|    1 | Ramesh   |   1230 |  
|    2 | Khilan   |   1560 | 
|    3 | kaushik  |   3000 |  
|    3 | kaushik  |   1500 |  
|    4 | Chaitali |   2060 |  
|    5 | Hardik   |   1569 |   
|    6 | Komal    |   2548 |  
|    7 | Muffy    |   1256 |  
+------+----------+--------+

How can I achieve this please help.

Upvotes: 0

Views: 157

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Hmmm . . . It seems like you want to unpivot the data:

select t.id, t.name, v.scale
from @mytable t cross apply
     (values (t.scale1), (t.scale2)) v(scale)
where v.scale is not null;

Upvotes: 1

Zhorov
Zhorov

Reputation: 29993

Another possible approach is ISNULL() (and it's an additional option if there are values in both s1.Amount and s2.Amount columns):

Insert into @myData (Id,Name,Scale1,Scale2)
Select 
   I.Id,
   I.Name,
   ISNULL(S1.Amount, 0) + ISNULL(S2.Amount, 0) AS Amount
From Information I 
Left Join Scale1 S1 on I.Scale1Id = S1.Id
Left Join Scale2 S2 on I.Scale2Id = S2.Id

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

Use coalesce()

Select 
I.Id,
I.Name,
coalesce(S1.Amount,S2.Amount) as amount
From Information I 
Left Join Scale1 S1 on I.Scale1Id = S1.Id
Left Join Scale2 S2 on I.Scale2Id = S2.Id

Upvotes: 2

GMB
GMB

Reputation: 222622

You can use coalesce():

select 
    i.id,
    i.name,
    coalesce(s1.amount, s2.amount) scale
from information i 
left join scale1 s1 on i.scale1id = s1.id
left join scale2 s2 on i.scale2id = s2.id

This gives priority to the value in the first left joined table, and falls back on the other one.

Upvotes: 2

Related Questions