Crezzer7 Crezzer7 - 6 months ago 8
SQL Question

SQL Server - multiple tables columns in 1 view and under 1 column header

Is it possible to do the following:

I have 2 tables called

Holidays
and
Allocations
, both of which contain a
startDate
and
endDate
field. I want to create a view which will display the
startDate
and
endDate
fields from both of these tables, but under the same column headers if possible, can this be done? or do I need to create a single table to handle this?

My theory behind using a view is that this will avoid the 1 large table storing a lot more columns, of which will contain null's where certain fields are not required.

Answer

Yes, you can do it in view by using UNION

CREATE VIEW [dbo].[ViewHolidayAllocation]
AS

SELECT 
    ROW_NUMBER() OVER(ORDER BY Id) AS RowNum, 
    * 
FROM
(
    SELECT Id, startDate, endDate FROM Holidays
    UNION
    SELECT Id, startDate, endDate FROM Allocations
) AS result