Combine columns from three different tables into a single column

2
votes

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!

3
sqlsql-server-2012
so you want each table's columns concatenated into a single string, and then you want to combine all rows from all tables, alternating tables in each row? (And how do you expect anything to be sorted?) – Amit
Yes, I am not worried about the order. But, they should have all been combined into a single column output. – user5000569

3 Answers

1
votes

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

1
votes

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.

1
votes

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.