CODEWITHSUNDEEP
sqlsql-server-2012
user5000569
user5000569

Reputation:

Combine columns from three different tables into a single column

I am new to SQL and I am not sure what to Google. I have three tables with different numbers of columns. I would like to combine these following three tables into a single column(no duplicates).

Table1

Col1          Col2            Col3
1              a               aa
2              b               ab
3              c               bb

Table2

Col1         Col2
123          Test
456          Test2
346          Test3

Table3

Col1         Col2        Col3       Col4
5695         93234       ABC        CDE
4534         92349       MSF        KSK
3244         12323       SLE        SNE

Expected Output:

FileOutput
1aaa
123Test
569593234ABCCDE
2bab
456Test2
453492349MSFKSK
...

Any help would be much appreciated. Thanks!

Upvotes: 2

Views: 130

Answers (3)

FutbolFan
FutbolFan

Reputation: 13723

The term you would want to Google would be: UNION and CONCAT.

Note: CONCAT is not supported in prior versions to SQL Server 2012.

To get your expected output, I would do this:

select 
  concat(cast(col1 as varchar(10)),col2,col3) as FileOutput 
from table1
UNION
select 
  concat(cast(col1 as varchar(10)),col2) as FileOutput 
from table2
UNION
select 
  concat(cast(col1 as varchar(10)),cast(col2 as varchar(10)),col3,col4) as FileOutput 
from table3

SQL Fiddle Demo

Upvotes: 1

APH
APH

Reputation: 4154

You can combine them using + (may need to cast your ints as varchars for this to work), then put them all in one table using union all. Example:

Select cast(col1 as varchar(100)) + col2 + col3
from Table1
union all
select cast(col1 as varchar(100)) + col2
from Table2

etc.

Note: be sure to use union all rather than union if you want to keep any duplicates you may create.

Upvotes: 1

Kritner
Kritner

Reputation: 13765

Not sure how you would parse the data, but you could do this:

select convert(varchar(100), col1) + convert(varchar(100), col2) + convert(varchar(100), col3) as fileOutput
from table1
union all 
select convert(varchar(100), col1) + convert(varchar(100), col2) as fileOutput
from table2
union all 
select convert(varchar(100), col1) + convert(varchar(100), col2) + 
convert(varchar(100), col3) + convert(varchar(100), col4) as fileOutput
from table4

note not knowing your column data types, your varchar(100) may need to expand, or could potentially shrink depending on your data.

Upvotes: 1

Related Questions