Combining Values

[imani_technology_spam]

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?

[Ed Murphy]

im*******************@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.

[imani_technology_spam]

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 <[e-mail]:

[e-mail] 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 -

[Madhivanan]

If you cant do this in reports, refer this

http://sqljunkies.com/WebLog/amachan...x?Pending=true

Madhivanan
On Jun 30, 10:47 am, "[e-mail]"
<[e-mail]:

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 <[e-mail]:

[e-mail] 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 -

[Marcin A. Guzowski]

im*******************@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/micros...ab9fecb969f34/

Third method:
http://sqlblogcasts.com/blogs/tonyro...07/06/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info

[imani_technology_spam]

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"
<[e-mail]:

[e-mail] 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/micros...r.programming/...

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

--
Best regards,
Marcin Guzowskihttp://guzowski.info

[Stephen2]

On Jul 2, 12:48 am, "[e-mail]"
<[e-mail]:

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"

<[e-mail]:

[e-mail] 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/micros...r.programming/...

Third method:http://sqlblogcasts.com/blogs/tonyro...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.

[imani_technology_spam]

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 <[e-mail]:

On Jul 2, 12:48 am, "[e-mail]"

<[e-mail]:

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"

<[e-mail]:

[e-mail] 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/micros...r.programming/...

Third method:http://sqlblogcasts.com/blogs/tonyro...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 -