By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,568 Members | 1,688 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,568 IT Pros & Developers. It's quick & easy.

Consolidating Records

P: n/a
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?

Sep 26 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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/

Sep 26 '05 #2

P: n/a
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
Sep 26 '05 #3

P: n/a
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


Sep 26 '05 #4

P: n/a
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?

Sep 26 '05 #5

P: n/a
SQL
In that case you can write a while loop or a cursor
Is this a one time thing?

http://sqlservercode.blogspot.com/

Sep 26 '05 #6

P: n/a
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/


Sep 27 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.