CODEWITHSUNDEEP
sqlsql-server
user8675722
user8675722

Reputation:

SQL joining tables and merging columns of different type

This may not be usual and probably is not good thing to do, but I would like to know if it is possible. So let's say I got two tables that look like this:

T1: A--B--C--D     T2:  A--B--E--F
    1 |2 |3 |4         '1'|'1'|1|1
    2 |2 |1 |4         '2'|'2'|2|2

Is there a way to join those two tables and to get in the result table merged columns A and B and also separated columns C, D, E, F. The main point is that column A and B in the T1 are of Integer type and in T2 of varchar and that is why I want those columns in result table to become varchar too.
The result table should look something like this:

T3: A--B--C--D--E--F     
   '1'|'2'|3 |4 |? |?        
   '2'|'2'|1 |4 |? |?
   '1'|'1'|? |? |1 |1
   '2'|'2'|? |? |2 |2

Upvotes: 1

Views: 58

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

another way is to use UNION ALL. may be you get some performance.

Also please correct if any type conversion error.

select cast(a as varchar)a,cast(b as varchar)b,cast(c as varchar)c
,cast(d as varchar)d,null e,null f 
from T1
union ALL
select a,b,null c,null d, e,f 
from T2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use a full join:

select coalesce(cast(t1.a as varchar(255)), t2.a) as a, 
       coalesce(cast(t1.b as varchar(255)), t2.b) as b,
       t1.c, t1.d, t2.e, t2.f
from t1 full join
     t2
     on t1.a = t2.a and t1.b = t2.b;

Note that mixing of data types can make the joins much slower. In addition, you can get type conversion errors if the values are not actually compatible (i.e. if the string columns do not contain numbers).

EDIT:

I don't actually like implicit conversion, so you can explicitly do the conversion:

select coalesce(cast(t1.a as varchar(255)), t2.a) as a, 
       coalesce(cast(t1.b as varchar(255)), t2.b) as b,
       t1.c, t1.d, t2.e, t2.f
from t1 full join
     t2
     on cast(t1.a as varchar(255)) = t2.a and cast(t1.b as varchar(255)) = t2.b;

Upvotes: 1

SqlZim
SqlZim

Reputation: 38033

Using convert() to change the data type, and quotename() to wrap the integer in single quotes like the varchar data type in a full outer join:

select 
    a = coalesce(t2.a,quotename(convert(varchar(5),t1.a),''''))
  , b = coalesce(t2.b,quotename(convert(varchar(5),t1.b),''''))
  , t1.c
  , t1.d
  , t2.e
  , t2.f
from t1
  full outer join t2
    on convert(varchar(5),t1.a) = t2.a
   and convert(varchar(5),t1.b) = t2.b

rextester demo: http://rextester.com/HSDSS14733

returns: 

+-----+-----+------+------+------+------+
|  a  |  b  |  c   |  d   |  e   |  f   |
+-----+-----+------+------+------+------+
| '1' | '2' | 3    | 4    | NULL | NULL |
| '2' | '2' | 1    | 4    | NULL | NULL |
| '1' | '1' | NULL | NULL | 1    | 1    |
| '2' | '2' | NULL | NULL | 2    | 2    |
+-----+-----+------+------+------+------+

Upvotes: 1

Related Questions