Create View multiple tables and different column names

Question

I have 2 tables in my database:

DetectorStatus

DetectorStatusID    DetectorID    DateTime    DetectorModeID    Status
      1                471          time            2            0.7

StationStatus

StationStatusID    DetectorID    DateTime    StationModeID    Status
      1                1541        time            2           0.74 

I want to create a view that looks like this:

StationStatusID    DetectorStatusID    DetectorID    DateTime    StationModeID    DetectorModeID    Status
      NULL                 1               471         time          NULL                2            0.7
      1                    NULL            1541        time          2                   NULL         0.74

Now, when i create the view like this:

CREATE view statusoverview AS
    SELECT * FROM [GMS_MAN].[dbo].[DetectorStatus]
    UNION ALL
    SELECT * FROM [GMS_MAN].[dbo].[StationStatus]

I get all the results inside 1 table. However StationModeID is inside DetectorModeID etc.

How do i create a view that looks like the given example?

Answer 1

You need to ensure that both result sets contain all of the columns you want:

CREATE view statusoverview AS
SELECT null as StationStatusID,
       DetectorStatusID,
       DetectorID,
       DateTime,
       null as StationModeID,
       DetectorModeID,
       Status
FROM [GMS_MAN].[dbo].[DetectorStatus]
UNION ALL
SELECT StationStatusID,
       null,
       DetectorID,
       DateTime,
       StationModeID,
       null,
       Status
FROM [GMS_MAN].[dbo].[StationStatus]

Note that the column names are taken from the first query, so it needs to name all of the columns. The second can omit naming the columns it's supplying nulls for.