Views offer a simple way to add basic yet useful functionality to stored queries, such as join or relate tables, and rename, compute, or perform aggregate functions on columns.

For instance, you may need to provide an alias for the column names that will appear in the view, in the following cases:

• when concatenating, or combining values from two or more columns into a single column,
• when a column is derived from a math expression, a function or a constant,
• when two or more columns may otherwise have the same name (usually because of a join), or
• you simply want to name a column in a view different from the column from which it is derived.

CREATE VIEW provides an optional argument for this.

CREATE VIEW viewName (column, ... ) AS query ...

where column is the name to be used for a column in the view. If not specified, the view columns acquire the same names as the columns in the SELECT statement.

NOTE: Column aliases can also be assigned in the SELECT statement.

This example defines a simple view with renamed columns from a two-table join.

CREATE VIEW [nw_Products Inventory] (Category, Product, Packaging, InStock, Stopped) 
AS 
SELECT CategoryName, ProductName, QuantityPerUnit, UnitsInStock, Discontinued 
   FROM nw_Categories C 
   INNER JOIN nw_Products P ON C.CategoryID = P.CategoryID

Products Inventory Run Sample | View Source

This example defines a view from a two-table join using a simple subquery that includes the aggregate average function (Avg()).

CREATE VIEW [nw_Products Above Average Price] 
AS 
SELECT CategoryName, ProductName, UnitPrice 
   FROM nw_Products P 
   INNER JOIN nw_Categories C ON C.CategoryID = P.CategoryID 
   WHERE UnitPrice > ( SELECT AVG ( UnitPrice ) FROM nw_Products )

Products Above Average Price Run Sample | View Source

This example defines a view with computed and renamed columns from a three-table join.

CREATE VIEW [pubs_Sales by Store] (Store, [Order Date], Title, Sale) 
AS 
SELECT stor_name, ord_date, title, qty*price 
   FROM pubs_Sales S 
   INNER JOIN pubs_stores ST ON S.stor_id = ST.stor_id 
   INNER JOIN pubs_titles T ON T.title_id = S.title_id

Pubs Sales by Store Run Sample | View Source

A view can reference another view, and this is where views can really be handy. Consider the following scenario.

This first example defines a view that returns the total sales generated by each product in the Northwind database. The view includes a computed column which calculates the UnitPrice multiplied by Quantity less Discount from the Order Details table. The view also applies the aggregate function Sum() to the computed columnm, grouped by ProductName. The resultant summary column is named ProductSales.

CREATE VIEW [nw_Sales by Product] 
AS 
SELECT C.CategoryName, P.ProductName, Sum ( 
      CONVERT ( money, ( OD.UnitPrice*Quantity* ( 1-Discount ) /100 ) ) *100 ) 
      AS ProductSales 
   FROM nw_Categories C 
   INNER JOIN nw_Products P ON C.CategoryID = P.CategoryID 
   INNER JOIN [nw_Order Details] OD ON P.ProductID = OD.ProductID 
   GROUP BY C.CategoryName, P.ProductName

Now this second example defines a view that returns the total sales generated by each product category. Here, we simply Sum() the ProductSales column referenced from the [Sales by Product] view above, grouped by CategoryName.

CREATE VIEW [nw_Sales by Category] 
AS 
SELECT [nw_Sales by Product].CategoryName, Sum ( 
      [nw_Sales by Product].ProductSales ) AS CategorySales 
   FROM [nw_Sales by Product] 
   GROUP BY [nw_Sales by Product].CategoryName

Sales by Product Run Sample | View Source

Sales by Category Run Sample | View Source