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

Collation troubles with SQLServer Express 2005

Greetings,
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.

Hi Ivan,

Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.

I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.

Thanks