|
Home > Archive > Microsoft SQL Server forum > September 2005 > Consolidating Records
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Consolidating Records
|
|
| imani_technology_spam@yahoo.com 2005-09-26, 8:24 pm |
| 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/
| |
| Simon Hayes 2005-09-26, 8:24 pm |
| imani_technology_spa
m@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@yahoo.com 2005-09-26, 8:24 pm |
| 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_spa
m@yahoo.com wrote:
>
> 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@yahoo.com 2005-09-26, 8:24 pm |
| 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?
| |
|
|
| imani_technology_spam@yahoo.com 2005-09-27, 9:23 am |
| 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/
|
|
|
|
|