How to create a valid select statement for SQL Server as external data source for SharePoint lists? 

The valid syntax of the select statements depends on the types of data sources used. Below there are given some samples for Microsoft SQL Server data sources, that are often used with the SharePoint Business Data List Connector (BDLC).

For a complete description of SQL select statement syntax see the product manual. On this page samples are given only:

A. Using SELECT to retrieve rows and columns

The following example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the Product table in the AdventureWorks database.

SELECT * FROM Production.Product ORDER BY Name ASC

Or the alternate way.

SELECT p.*  FROM Production.Product AS p ORDER BY Name ASC

This example returns all rows (no WHERE clause is specified), and only a subset of the columns (Name, ProductNumber, ListPrice) from the Product table in the AdventureWorks database. Additionally, a column heading is added. In the SharePoint List "Price" will be used as column name instead of "ListPrice" in the orifinal table or view.

SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC

This example returns only the rows for Product that have a product line of R and that have days to manufacture that is less than 4.

SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC

B. Using SELECT with column headings and calculations

The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.

SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice),Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductID ORDER BY ProductName DESC

This is the query that calculates the revenue for each product in each sales order.

SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductID ORDER BY ProductName ASC

C. Using DISTINCT with SELECT

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

SELECT DISTINCT Title FROM HumanResources.Employee ORDER BY Title

D. Creating tables with SELECT INTO

This select type is not supported by the SharePoint Business Data List Connector.

 E. Using correlated subqueries

The following example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery that retrieves one instance of each product name for which the product model is a long sleeve logo jersey, and the ProductModelID numbers match between the Product and ProductModel tables.

SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS    (SELECT *     FROM Production.ProductModel AS pm      WHERE p.ProductModelID = pm.ProductModelID           AND pm.Name = 'Long-sleeve logo jersey')

SELECT DISTINCT Name FROM Production.ProductWHERE ProductModelID IN    (SELECT ProductModelID      FROM Production.ProductModel     WHERE Name = 'Long-sleeve logo jersey')

The following example uses IN in a correlated, or repeating, subquery. This is a query that depends on the outer query for its values. The query is executed repeatedly, one time for each row that may be selected by the outer query. This query retrieves one instance of the first and last name of each employee for which the bonus in the SalesPerson table is 5000.00 and for which the employee identification numbers match in the Employee and SalesPerson tables.

SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact AS c JOIN HumanResources.Employee AS e    ON e.ContactID = c.ContactID WHERE 5000.00 IN    (SELECT Bonus     FROM Sales.SalesPerson AS sp     WHERE e.EmployeeID = sp.SalesPersonID)

The previous subquery in this statement cannot be evaluated independently of the outer query. It requires a value for Employee.EmployeeID, but this value changes as the SQL Server Database Engine examines different rows in Employee. A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the product models for which the maximum list price is more than twice the average for the model.

SELECT p1.ProductModelIDFROM Production.Product AS p1GROUP BY p1.ProductModelIDHAVING MAX(p1.ListPrice) >= ALL(SELECT 2 * AVG(p2.ListPrice)FROM Production.Product AS p2WHERE p1.ProductModelID = p2.ProductModelID)

This example uses two correlated subqueries to find the names of employees who have sold a particular product.

SELECT DISTINCT c.LastName, c.FirstName FROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE EmployeeID IN (SELECT SalesPersonID FROM Sales.SalesOrderHeaderWHERE SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderDetailWHERE ProductID IN (SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42')))

F. Using GROUP BY

The following example finds the total of each sales order in the database.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotalFROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID

Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.  

G. Using GROUP BY with multiple groups

The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.

SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price',     SUM(LineTotal) AS SubTotalFROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID

H. Using GROUP BY and WHERE

The following example puts the results into groups after retrieving only the rows with list prices greater than $1000.

SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price' FROM Production.Product WHERE ListPrice > $1000GROUP BY ProductModelID ORDER BY ProductModelID

I. Using GROUP BY with an expression

The following example groups by an expression. You can group by an expression if the expression does not include aggregate functions.

SELECT AVG(OrderQty) AS 'Average Quantity', NonDiscountSales = (OrderQty * UnitPrice)FROM Sales.SalesOrderDetailGROUP BY (OrderQty * UnitPrice)ORDER BY (OrderQty * UnitPrice) DESC

J. Using GROUP BY with ORDER BY

The following example finds the average price of each type of product and orders the results by average price.

SELECT ProductID, AVG(UnitPrice) AS 'Average Price'FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice)

K. Using the HAVING clause

The first example that follows shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The second example shows a HAVING clause without aggregate functions.

SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID

This query uses the LIKE clause in the HAVING clause.

SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetailGROUP BY SalesOrderID, CarrierTrackingNumberHAVING CarrierTrackingNumber LIKE '4BD%'ORDER BY SalesOrderID

L. Using HAVING and GROUP BY

The following example shows using GROUP BY, HAVING, WHERE, and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ProductID.

SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID

M. Using HAVING with SUM and AVG

The following example groups the SalesOrderDetail table by product ID and includes only those groups of products that have orders totaling more than $1000000.00 and whose average order quantities are less than 3.

SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3

To see the products that have had total sales greater than $2000000.00, use this query:

SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00

If you want to make sure there are at least one thousand five hundred items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold. The query looks like this:

SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500

N. Calculating group totals by using COMPUTE BY

The following example uses two code examples to show the use of COMPUTE BY. The first code example uses one COMPUTE BY with one aggregate function, and the second code example uses one COMPUTE BY item and two aggregate functions. This query calculates the sum of the orders, for products with prices less than $5.00, for each type of product.

SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal) BY ProductID

This query retrieves the product type and order total for products with unit prices under $5.00. The COMPUTE BY clause uses two different aggregate functions.

SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, LineTotal COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID

O. Calculating grand values by using COMPUTE without BY

The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on. The following example finds the grand total of the prices and advances for all types of products les than $2.00.

SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $2.00 COMPUTE SUM(OrderQty), SUM(LineTotal)

You can use COMPUTE BY and COMPUTE without BY in the same query. The following query finds the sum of order quantities and line totals by product type, and then computes the grand total of order quantities and line totals.

SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00ORDER BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID COMPUTE SUM(OrderQty), SUM(LineTotal)

P. Calculating computed sums on all rows

The following example shows only three columns in the select list and gives totals based on all order quantities and all line totals at the end of the results.

SELECT ProductID, OrderQty, LineTotal FROM Sales.SalesOrderDetail COMPUTE SUM(OrderQty), SUM(LineTotal)

Q. Using more than one COMPUTE clause

The following example finds the sum of the prices of all orders whose unit price is less than $5 organized by product ID and order quantity, as well as the sum of the prices of all orders less than $5 organized by product ID only. You can use different aggregate functions in the same statement by including more than one COMPUTE BY clause.

SELECT ProductID, OrderQty, UnitPrice, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID, OrderQty, LineTotal COMPUTE SUM(LineTotal) BY ProductID, OrderQty COMPUTE SUM(LineTotal) BY ProductID

R. Comparing GROUP BY with COMPUTE

The first example that follows uses the COMPUTE clause to calculate the sum of all orders whose product's unit price is less than $5.00, by type of product. The second example produces the same summary information by using only GROUP BY.

SELECT ProductID, LineTotal FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 ORDER BY ProductID COMPUTE SUM(LineTotal) BY ProductID

This is the second query that uses GROUP BY.

SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID ORDER BY ProductID

S. Using SELECT with GROUP BY, COMPUTE, and ORDER BY clauses

The following example returns only those orders whose unit price is less than $5, and then computes the line total sum by product and the grand total. All computed columns appear within the select list. 

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty COMPUTE SUM(SUM(LineTotal));

T. Using the INDEX optimizer hint

(see manual)

U. Using OPTION and the GROUP hints

(see manual)

V. Using the UNION query hint

(see manual)

W. Using a simple UNION

In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.

SELECT ProductModelID, Name FROM Production.ProductModel UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name

X. Using SELECT INTO with UNION

(not supported in BDLC) 

Y. Using UNION of two SELECT statements with ORDER BY

(see manual)

READY TO GO NEXT STEPS?