ruedi ruedi - 5 months ago 11
SQL Question

How to get two tables among each other?

I have a table that holds the columnnames of different datasets. E.g.

ID|Col1 |Col2 |Col3|
------------------------
23|Name |City |Age |
24|Answer1|Answer2|Name|


I have another table that holds data for each of the columns in the table above.

ID|Col1 |Col2 |Col3|
------------------------
23|Marc |Paris |45 |
23|Frank |Berlin |14 |
23|Ole |Oslo |65 |
24|Yes |Yes |Alex|
24|No |Yes |Thea|


I am looking for a way to get a table with a
where = ID
with the first column holding the column names and then the data that belong to that column. E.g.

ID|Col1 |Col2 |Col3|
------------------------
23|Name |City |Age |
23|Marc |Paris |45 |
23|Frank |Berlin |14 |
23|Ole |Oslo |65 |


I already tried different Joins but all I got was all data in one column not among each other. Could anyone help me here?

I am working with
SQL Server
so using the
TSQL
dialect but I guess the answer has not be specific to
TSQL
so I added the
SQL
flag as well.

Answer
SELECT Col1, Col2, Col3, 1 as [sort]
from table1 
where id = 23 
union all 
SELECT Col1, Col2, Col3, 2 
from table2
where id = 23  
order by sort