Creating a View with Data from Multiple Tables in SQL Server

Q

Can You Create a View with Data from Multiple Tables in SQL Server?

✍: FYIcenter.com

A

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
   SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
      c.FirstName, c.LastName, c.CompanyName
   FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
   WHERE o.CustomerID = c.CustomerID
GO

SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName 
   FROM SalesOrderView;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71915          2361.6403   Aerobic Exercise Company
SO71938          98138.2131  Bulk Discount Store
SO71783          92663.5609  Eastside Department Store
SO71899          2669.3183   Coalition Bike Company
SO71898          70698.9922  Instruments and Parts Company
SO71902          81834.9826  Many Bikes Store
SO71832          39531.6085  Closest Bicycle Store
SO71776          87.0851     West Side Mart
SO71797          86222.8072  Riding Cycles
SO71895          272.6468    Futuristic Bikes

 

Understanding and Managing Views in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-05, 355👍, 0💬