|
 Variable PIVOTING with Self-Terminating WHILE Loop UPDATE
By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science,
MCDBA, MCITP
October 7, 2007
SQL Server 2005 has introduced the PIVOT function which can be used to flip rows into columns in a fixed layout format.
What on the other hand if the number of members across columns is a variable? Well, actually there is an extremely neat WHILE-UPDATE solution which even works in SQL Server 2000. It is very fast.
Assume we want to list all the EmployeeID-s for employees living in a certain city for AdventureWorks fictional mountain bike manufacturer.
First we create a typical long SQL vertical listing where employees live.
USE AdventureWorks;
SELECT City = City + ', ' + StateProvinceCode,
e.EmployeeID
INTO #Location
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress a
ON e.EmployeeID = a.EmployeeID
INNER JOIN Person.Address ad
ON a.AddressID = ad.AddressID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = ad.StateProvinceID
ORDER BY City,
EmployeeID;
SELECT TOP 5 * FROM #Location
/*
City EmployeeID
Bellevue, WA 9
Bellevue, WA 21
Bellevue, WA 22
Bellevue, WA 23
Bellevue, WA 27
*/
Second step, we select distinct cities and prepare a string to hold the employee list for each city separated by "|". Naturally, the string is finite. Usually, we don’t deal with thousands of list members in this kind of PIVOTING. If we do, we can use varchar(max) from SQL Server 2005 on.
SELECT DISTINCT City,
Employees = convert(VARCHAR(2000),'|')
INTO #City
FROM #Location;
SELECT TOP 5 * FROM #City
/*
City Employees
Bellevue, WA |
Berlin, HH |
Bordeaux, 33 |
Bothell, WA |
Calgary, AB |
*/
Finally, we are ready for the dazzling WHILE-UPDATE PIVOTING. This construct is much-much faster than using a function. The loop self-terminates when there are no more UPDATEs to be done.
WHILE @@ROWCOUNT > 0
BEGIN
UPDATE c
SET c.Employees = c.Employees + convert(VARCHAR(3),l.EmployeeID) + '|'
FROM #City c
INNER JOIN #Location l
ON c.City = l.City
AND charindex('|' + convert(VARCHAR(3),l.EmployeeID) + '|',
c.Employees,1) = 0
END;
To check out what happened we run a SELECT:
SELECT TOP 7 * FROM #City ORDER BY City DESC
/* Partial results
City Employees
Snohomish, WA |10|32|36|45|92|134|138|187|230|246|
Seattle, WA |7|18|25|29|31|43|51|52|53|56|57|64|73|83|85|86|...
San Francisco, CA |217|281|
Sammamish, WA |50|71|78|84|93|113|124|125|133|201|211|219|…
Renton, WA |11|12|34|77|121|140|145|195|198|218|225|238|241|259|…
Redmond, WA |3|6|19|42|48|55|129|131|132|146|157|189|192|197|…
Portland, OR |280|
*/
Related articles:
Using While Loop for SQL Server Update
SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE
|
