|
|
Posted by Erland Sommarskog on 07/02/07 22:00
imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> Sorry, I'm a little rusty on cursors. How would I pull this off? Can
> I (or should I) use nested WHILE loops?
First, which version of SQL Server are you on?
Next, can you make an assumption on the maximum number of addresses?
If you are on SQL 2005, consider the XML solution in Tony Rogerson's
blog. If there may be special XML characters in the data, it gets a little
messy, but I believe that Tony covers that in his post.
If you can assume that there are at most, say, five addresses, there is
one method that Marcin left out, run a pivot-type of query:
SELECT name, MIN(CASE adrno WHEN 1 THEN address END) +
coalesce(CASE adrno WHEN 2 THEN '; ' + address END) +
...
FROM tbl
GROUP BY name
If you are on SQL 2000 and a person can have umpteen addresses, it will
have to be a loop for you. Cursor or WHILE does not matter that much -
as long as you do the WHILE loop right. Here is the framework for a
cursor loop:
DECLARE cur INSENSITIVE CURSOR FOR
SELECT name, address
FROM tbl
ORDER BY name, adrno
OPEN cur
SELECT @prev_name = ''
WHILE 1 = 1
BEGIN
FETCH cur INTO @name, @address
IF @@fetch_status
BREAK
IF @name <> @prev_name
...
ELSE
...
END
DEALLOCATE cur
The count should not be any particular problem. You could simply run a
count query first and save that into a temp table, and then use that
data when you compose the rest.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|