Combining Values

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:

Quote:

Originally Posted by

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 <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

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:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

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/amacha...px?Pending=true
Madhivanan

On Jun 30, 10:47 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

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 <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

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:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Do I need a cursor for this?

>

Quote:

Originally Posted by

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

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/micro...cab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyr.../07/06/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"
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

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/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info

|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

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"
>
>
>
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

--
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 <Step...@.mailinator.comwrote:

Quote:

Originally Posted by

On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
>
>
>
>
>
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

The results need to be

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

I have no idea how to pull this off.

>

Quote:

Originally Posted by

On Jun 30, 6:39 am, "Marcin A. Guzowski"

>

Quote:

Originally Posted by

<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info-Hide quoted text -

>

Quote:

Originally Posted by

- 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 -

sqlsql