Consolidating Records

[imani_technology_spam]

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?

[SQL]

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/

[Simon Hayes]

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

[imani_technology_spam]

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:

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

[imani_technology_spam]

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?

[SQL]

In that case you can write a while loop or a cursor
Is this a one time thing?

http://sqlservercode.blogspot.com/

[imani_technology_spam]

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/