DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

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

 

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE