Posted by Simon Hayes on 09/26/05 22:58

imani_technology_spam@yahoo.com wrote:
> Let's say I have two tables:
>
> CREATE TABLE dbo.OldTable
> (
> OldID int NOT NULL,
> OldNote varchar(100) NULL
> ) ON [PRIMARY]
> GO
>
>
> AND
>
> CREATE TABLE dbo.NewTable
> (
> NewID int NOT NULL IDENTITY (1, 1),
> OldID int NULL,
> ComboNote varchar(255) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE dbo.NewTable ADD CONSTRAINT
> PK_NewTable PRIMARY KEY CLUSTERED
> (
> NewID
> ) ON [PRIMARY]
>
> GO
>
> OldTable's data looks like this:
>
> OldID OldNote
> ----- -------
> 1 aaa
> 2 bbb
> 3 ccc
> 2 ddd
> 4 eee
>
>
> NewTable's data (which is derived from the OldTable) should look like
> this:
>
> NewID OldID ComboNote
> ----- ----- ---------
> 1 1 aaa
> 2 2 bbb + char(13) + ddd
> 3 3 ccc
> 4 4 ddd
>
> How can I combine the notes from OldTable where two (or more) records
> have the same OldID into the NewTable's ComboNote?
>

You could look at a crosstab query, but if the number of old rows for
each new row is unknown, then it's quite awkward to do in pure TSQL. A
cursor might be the best server-side solution, although using a
client-side script may be easier.

But storing multiple values in a single column is usually bad design,
and it's often difficult to query columns like that efficiently. Perhaps
you should consider generating and formatting ComboNote in the front end
when you retrieve it, rather than storing it in the database, but
obviously I don't know your environment and application, so you may have
a good reason for keeping it as a single column.

Simon

[Back to original message]