Using SQL Cursors
Using a Cursor in T-SQL
This tutorial describes how to use a cursor to iterate through rows of data to construct a single entry into a table of order IDs and Totals for each Sales Person from a view. Using the AdventureWorks database, a view, a table and code will be created to produce the final deliverable: an Excel report containing the compressed data in a simple, easy to read format.
Step 1: Create the SQL View to Retrieve the Data
Using the AdventureWorks database, the tables: SalesPerson and SalesOrderHeader, Contact and Employee, the view to retreive the data can be created.
CREATE VIEW [dbo].[vw_SalesPersonOrders]
AS
SELECT Sales.SalesPerson.SalesPersonID, Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.OrderDate,
Person.Contact.FirstName, Person.Contact.LastName
FROM Sales.SalesPerson
INNER JOIN
Sales.SalesOrderHeader ON Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN
HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID
INNER JOIN
Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID
The data from the created View looks like this:
Step 2: Construct the SQL Cursor Code
Create code to iterate through the SQL View line by line to construct a single entry for each Sales Person.
Code some variables to hold the current and previous data, including the salesId, orderID and totals. The previous values will become the current values after each pass throught the cursor. The salesID will be compared through each iteration. When a different Sales Person ID is found, an Insert of the concatenated orders and totals will be made into a table.
Declare @salesid_curr int
Declare @salesid_prev int
Declare @salesName_prev varchar(100)
Declare @salesName_curr varchar(100)
Declare @orderid_curr int
Declare @orderid_prev int
Declare @total_curr money
Declare @total_prev money
Declare @total varchar(7000)
Declare @orderIDs varchar(3000)
Declare @salesID int
Include a statement to Truncate the table (to be created) before doing any work.
TRUNCATE TABLE dbo.SalesPersonOrders
Write the code to Declare and Open a Cursor and then Fetch the data into the cursor while the @@FETCH_STATUS = 0.
Notes: the code is:
- Iterating through the data using NoLock since no transactions are taking place
- Using Else If statements to capture row 1, rows with the same ID and rows with different IDs and appending or INSERTing the date appropriately.
- Concatenating the information into OrderIDs & TotalAmts using a Convert function and Char (10) + Char (13) to provide a line break when the data is imported to Excel
--declare the cursor
DECLARE sales_cursor CURSOR FOR
SELECT SalesPersonID, FirstName + ' ' + LastName, SalesOrderID, TotalDue
FROM vw_SalesPersonOrders with(nolock)
WHERE OrderDate BETWEEN '2004-05-01' and '2004-06-30'
ORDER BY SalesPersonID, SalesOrderID
OPEN sales_cursor
--set the salesIds to 0
Set @salesid_curr=0
Set @salesid_prev=0
--get the first record from the cursor and populate the variables
FETCH NEXT FROM sales_cursor
INTO @salesid_curr, @salesName_curr, @orderid_curr, @total_curr
--iterate through the cursor until last row
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@salesid_prev = 0) --get the 1st record
BEGIN
SET @salesid_prev = @salesid_curr
SET @salesName_prev = @salesName_curr
SET @orderid_prev = @orderid_curr
SET @total_prev = @total_curr
SET @salesID = @salesID_curr
SET @orderIDs = IsNull(@orderIDs,'') + CHAR(10) + char(13) +
convert(varchar(6),@orderid_curr)
SET @total = IsNull(@total,'') + CHAR(10) + CHAR(13) + CONVERT(varchar(15), @total_curr)
END
ELSE IF (@salesid_prev = @salesid_curr)
BEGIN
SET @salesid_prev = @salesid_curr
SET @salesName_prev = @salesName_curr
SET @orderid_prev = @orderid_curr
SET @total_prev = @total_curr
SET @salesID = @salesID_curr
SET @orderIDs = IsNull(@orderIDs,'') + char(10) + char(13) +
convert(varchar(6),@orderid_curr)
SET @total = IsNull(@total,'') + CHAR(10) + CHAR(13) + CONVERT(varchar(15), @total_curr)
END
--compare and if different, do an INSERT
ELSE IF (@salesid_prev <> @salesid_curr and @salesid_prev > 0)
BEGIN
INSERT INTO dbo.SalesPersonOrders
(SalesPersonID, SalesPersonName, SalesOrderID, TotalDue)
VALUES
(@salesID, @salesName_prev, @orderIDs, @total)
SET @salesid_prev = @salesid_curr
SET @salesName_prev = @salesName_curr
SET @orderid_prev = @orderid_curr
SET @total_prev = @total_curr
SET @salesID = @salesID_curr
SET @orderIDs = convert(varchar(6),@orderid_curr)
SET @total = CONVERT(varchar(15), @total_curr)
END
FETCH NEXT FROM sales_cursor
INTO @salesid_curr, @salesName_curr, @orderid_curr, @total_curr
END
--catches the last SalesPerson
INSERT INTO dbo.SalesPersonOrders
(SalesPersonID, SalesPersonName, SalesOrderID, TotalDue)
VALUES
(@salesID, @salesName_prev, @orderIDs, @total)
Add code to close the cursor for clean up
CLOSE sales_cursor DEALLOCATE sales_cursor
Step 3: Create the Data Table
Write T-SQL Code to create the table receiving the INSERTs.
CREATE TABLE dbo.SalesPersonOrders(
SalesPersonID int NULL,
SalesPersonName varchar(100) NULL,
SalesOrderID varchar(3000) NULL,
TotalDue varchar(7000) NULL
)
Step 4: Create a SQL Server Connection from Excel
Create a new connection from Excel to the dbo.SalesPersonOrders table to retrieve the results as below. Go to the Data tab and select From SQL Server.
The Data Connection Wizard will open. Enter the name of the SQL Server to connect to the table.
Select the database name and then highlight the table name and click Finish
Click OK on the Pop up window to import the data to the first cell in the corner, $A$1.
Right click on the SalesOrderID and Total Due columns and select Format Cells. Click on the Alignment tab and click the Wrap Text checkbox to format the results as shown here.
This concludes this tutorial on how to concatenate data using a cursor.