Вопрос:

Create View multiple tables and different column names

sql view sql-server-2014

1340 просмотра

3 ответа

673 Репутация автора

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?

Автор: Mitch Источник Размещён: 06.03.2017 08:24

Ответы (3)


1 плюс

202452 Репутация автора

Решение

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.

Автор: Damien_The_Unbeliever Размещён: 06.03.2017 08:29

1 плюс

1691 Репутация автора

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

I might have do some column misspellings, but it iwll be something like that

Автор: Whencesoever Размещён: 06.03.2017 08:30

0 плюса

305 Репутация автора

Well, you won't use SELECT *, you will specify which columns you want to have in your table:

CREATE view statusoverview AS
SELECT detector.*, station.StationStatusID, station.StationMode,station.Status 
FROM DetectorStatus detector
LEFT JOIN StationStatus station ON station.DetectorID=detector.DetectorID.

Whatever you choose, I think you shouldn't "SELECT *, but specify what you need"

Автор: berthos Размещён: 06.03.2017 08:31
Вопросы из категории :
32x32