Consolidating Records

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?Something like this (untested)

select o1.OldID,o1.OldNote + char(13) + coalesce(o2.OldNote) as
ComboNote from OldTable o1
left join OldTable o2 on o1.OldID =o2.OldID
and o1.OldNote <> o2.OldNote

http://sqlservercode.blogspot.com/|||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|||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|||In that case you can write a while loop or a cursor
Is this a one time thing?

http://sqlservercode.blogspot.com/|||That's helpful, but what if there are more than two records that have
the same OldID that need to go into the NewTable's ComboNote?|||Yes, this should be a one-time thing. We are doing this to migrate
some data.

I think I'll take your advice and look into cursors, although I was
taught that cursors are the work of the devil.

SQL wrote:
> In that case you can write a while loop or a cursor
> Is this a one time thing?
> http://sqlservercode.blogspot.com/