sql server (alternate): I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

And combine them into this:

John Doe | Address 1; Address 2; Address 3


Do I need a cursor for this?

[quoted text, click to view]

Do this in the reporting layer (e.g. Crystal Reports) if at

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

[quoted text, click to view]

If you cant do this in reports, refer this

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan


On Jun 30, 10:47 am, "imani_technology_s...@yahoo.com"
[quoted text, click to view]

[quoted text, click to view]

You have three options:

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

Option 1) is the safest method.

For option 2) details refer to:
http://groups.google.pl/group/microsoft.public.sqlserver.programming/tree/browse_frm/thread/5ecab9fecb969f34/

Third method:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx


--
Best regards,
Marcin Guzowski

Here's where things get interesting:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

The results need to be

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

I have no idea how to pull this off.

On Jun 30, 6:39 am, "Marcin A. Guzowski"
[quoted text, click to view]

Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?

[quoted text, click to view]

On Jul 2, 12:48 am, "imani_technology_s...@yahoo.com"
[quoted text, click to view]

You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.

imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
[quoted text, click to view]

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