Posted by imani_technology_spam@yahoo.com on 09/26/05 23:59

I agree with you. Unfortunately, that is what the clients want and I
don't think they can be talked out of it.

Simon Hayes wrote:
> 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]