1

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations between these 2 tables. The columns are also differant. Here is an example:

tableA:                       tableB:

+-------------------------+   +--------------------------------+
| Name                        | Year
---------------------------   +---------------------------------
| A    |                      | 2000    |
| B    |                      | 2001    |
| C    |                      | 2002    |

I would like to have results like this:

 +-------------------------+   
    | Name | Year                   
    ------------------------
    | A    |                   
    | C    |
    | D    |
    |      | 2000
    |      | 2001
    |      | 2002

How can I realize this? I see only joins, union solutions, but they dont work for me because I dont have some relation between these 2 tables.

2 Answers 2

1
with cte1(Name, Year) as ( select Name, null as year from tablea),
cte2(Name, Year) as (select NULL as Name, year from tableb)
Select Name, Year from CTE1
UNION ALL
select Name, Year from CTE2

This should work, didn't read the question properly last answer :)

2
  • You're missing as in second cte.
    – AdamL
    Jul 3, 2014 at 12:36
  • Yeah written without developper :p that's better now :) Jul 3, 2014 at 12:37
0

If you wanted them randonly side by side you could use an adhoc ID value. If there are different numbers in each table then some will appear at the end without a partner.

with cte1 as
(
 select a1.name, ROW_NUMBER() OVER (ORDER BY a1.name) as ID from a1
),
cte2 as
(
select c1.theyear, ROW_NUMBER() OVER (ORDER by c1.theyear) as ID from c1
)
select cte1.name, cte2.theyear
from cte1
left outer join cte2 on cte1.id = cte2.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.