Variable PIVOTING with Self-Terminating WHILE – UPDATE
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

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 mountain mike manufacturer.

First we create a typical long SQL vertical listing where employees live.

select City=City+', '+StateProvinceCode, e.EmployeeID
into #Location
from HumanResources.Employee e
join HumanResources.EmployeeAddress a
on e.EmployeeID=a.EmployeeID
join Person.Address ad
on a.AddressID = ad.AddressID
join Person.StateProvince sp
on sp.StateProvinceID=ad.StateProvinceID
order by City, EmployeeID

Second step, we select distinct cities and prepare a string to hold the employee list for each city.  Naturally, the string is finite. Usually, we don’t deal with thousands of list members in this kind of PIVOTING.

select distinct City, Employees=convert(varchar(2000), '|')
into #City
from #Location

Finally, we are ready  for the dazzling WHILE-UPDATE PIVOTING.  This construct is much-much faster than using a function:

while @@rowcount > 0
update c set c.Employees = c.Employees + convert(varchar(3),l.EmployeeID)+'|'
from #City c
join #Location l
on c.City=l.City
and charindex('|'+convert(varchar(3),l.EmployeeID)+'|', c.Employees, 1) = 0

To check out what happened we run a SELECT:

select * from #City

The returned results: