Variable PIVOTING with Self-Terminating WHILE Loop UPDATE
By Kalman Toth, M.Phil., M.Phil., 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|

*/