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?
imani_technology_spam@yahoo.com wrote:
> 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?
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.
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.
On Jun 29, 6:55 pm, Ed Murphy
> imani_technology_s...@yahoo.com wrote:
> > 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?
>
> Do this in the reporting layer (e.g. Crystal Reports) if at
> all possible.- Hide quoted text -
>
> - Show quoted text -
If you cant do this in reports, refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/50 65.aspx?Pending=true
Madhivanan
On Jun 30, 10:47 am, "imani_technology_s...@yahoo.com"
> 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.
>
> On Jun 29, 6:55 pm, Ed Murphy
>
>
>
> > imani_technology_s...@yahoo.com wrote:
> > > 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?
>
> > Do this in the reporting layer (e.g. Crystal Reports) if at
> > all possible.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
imani_technology_spam@yahoo.com wrote:
> 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.
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.pro gramming/tree/browse_frm/thread/5ecab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/0 6/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info
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"
> imani_technology_s...@yahoo.com wrote:
> > 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.
>
> 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/...
>
> Third method:http://sqlblogcasts.com/blogs/tonyrogerson/archive/20 06/07/06/871.aspx
>
> --
> Best regards,
> Marcin Guzowskihttp://guzowski.info
On Jul 2, 12:48 am, "imani_technology_s...@yahoo.com"
> 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"
>
>
>
>
> > imani_technology_s...@yahoo.com wrote:
> > > 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.
>
> > 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/...
>
> > Third method:http://sqlblogcasts.com/blogs/tonyrogerson/archive/20 06/07/06/871.aspx
>
> > --
> > Best regards,
> > Marcin Guzowskihttp://guzowski.info- Hide quoted text -
>
> - Show quoted text -
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.
Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?
On Jul 2, 3:28 am, Stephen2
> On Jul 2, 12:48 am, "imani_technology_s...@yahoo.com"
>
>
>
>
>
>
> > 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"
>
> >
> > > imani_technology_s...@yahoo.com wrote:
> > > > 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.
>
> > > 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/...
>
> > > Third method:http://sqlblogcasts.com/blogs/tonyrogerson/archive/20 06/07/06/871.aspx
>
> > > --
> > > Best regards,
> > > Marcin Guzowskihttp://guzowski.info-Hide quoted text -
>
> > - Show quoted text -
>
> 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.- Hide quoted text -
>
> - Show quoted text -
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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx