CODEWITHSUNDEEP
sql-serverview
Jordan
Jordan

Reputation: 35

SQL Merge Two Views into One

I have two views: TableA and TableB which have very similar information to one another, however there are several unique columns.

Here are my two views and their table structure

I would like the final result to look like this

Where the original columns: Client ID, Invoice Number, Check Date, Year and Charge Description are simply stacked on top of one another. The columns: Charge Type, Billed Amount, Liability Amount and Total would also be stacked on one another but when there is no value in the corresponding view it defaults to null (or nothing).

I believe what I need to do is have some of the columns (the ones that are named identically) to union(?) but the rest to remain as they are and when there are no values for them to be populated with nulls (or even better, nothing). I am trying to get them into one table so that I can use a pivot table from my one data source (instead of two separate data sources).

Sorry for the poor formatting (I used images as I couldn't figure out how to use use |s properly).

Upvotes: 2

Views: 6980

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can create view as below:

create view yourview as 
    select clientid, [invoice number], [check date], [year], [Charge Description], Null as [Charge Type], Null as Total, [Billed Amount], [Liability Amount] from tableA
    union all
    select clientid, [invoice number], [check date], [year], [Charge Description],[Charge Type], Total,  Null as [Billed Amount],  Null as [Liability Amount] from tableB

Upvotes: 0

RSSM
RSSM

Reputation: 679

You can use a UNION ALL to return 2 queries as a single result set, if all the columns match. In your case, your columns do not completely match, but you are displaying null for any column mismatch - so in that case, you can just display null and the column name. (eg NULL as 'Total')

SELECT ClientID, InvoiceNumber, CheckDate, Year, ChargeDescription, 
       NULL as 'ChargeType', NULL as 'Total', BilledAmount, LiabilityAMount
FROM TABLEA
UNION ALL
SELECT ClientID, InvoiceNumber, CheckDate, Year, ChargeDescription, 
       ChargeType, Total, NULL as 'BilledAmount', NULL as 'LiabilityAmount'
FROM TABLEB

Upvotes: 4

Related Questions