hofmeister hofmeister - 2 months ago 8
SQL Question

How to join tables in rows instead of columns?

I have some problems with creating a database view. I would like to join several tables to a single view. I would like to get each column as a row, instead of a column.

Here is a small example of two tables:


Tables


| Table1: Id | Table1: Type | | Table2: Id | Table2:Type |
|:----------:|:------------:| |:----------:|:-----------:|
| 1 | A | | 1 | C |
| 1 | B | | 2 | D |
| 2 | B | | 2 | E |


If I use following SQL statement, I get as expected following result:


SQL Statement


SELECT
table1.id,
table1.Type AS Type1,
table2.Type AS Type2
FROM
table1
INNER JOIN table2 ON table2.id = table1.id



Result


| Id | Type1 | Type2 |
|:--: |:-----: |:-----: |
| 1 | A | C |
| 1 | B | C |
| 2 | B | D |
| 2 | B | E |


I knew that this is correct. But I would like to get something like:

| Id | Type |
|:--: |:-----: |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | B |
| 2 | D |
| 2 | E |


This view displays for each
Id
,
Type
combination a single row.

How could I joind tables in that way?

Answer

Try this:

SELECT id, Type FROM table1
UNION ALL
SELECT id, Type FROM table2
ORDER BY ID,TYPE

Result:

ID  TYPE
1   A
1   B
1   C
2   B
2   D
2   E

See result in SQL Fiddle.