make new field method - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

make new field method

Author

Topic

NTC
Starting Member

21 Posts

Posted - 2007-12-08 : 11:09:30

table that is being imported from an unknown system;

key Field1 Field2
1 X 105
2 abc 123
3 abc 111
4 btb aff
5 sym 0101
6 X 106
7 abc 144
8 sym 2121

No control over the structure of this incoming data....from a business/database perspective X is a unique record and everything between them belongs to its preceeding X. Right now the only way to know that ABC 123 is part of the X105 record (while ABC144 is part of X106 record) is because the autonumber key generated during the import puts them in sequential order.

The records quantity between Xs is variable.

One doesn't want to rely upon the autonumber.

So I need to run it thru a query to generate a better cross reference field and make a new table

key Field1 Field2 Field3
1 X 105 105
2 abc 123 105
3 abc 111 105
4 btb aff 105
5 sym 0101 105
6 X 106 106
7 abc 144 106
8 sym 2121 106

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-08 : 23:23:29

[code]
declare @t table (id int not null, col1 varchar(3) not null, col2 varchar(4) not null)

insert @t
select 1, 'X', '105' union all
select 2, 'abc', '123' union all
select 3, 'abc', '111' union all
select 4, 'btb', 'aff' union all
select 5, 'sym', '0101' union all
select 6, 'X', '106' union all
select 7, 'abc', '144' union all
select 8, 'sym', '2121'

select
t.id, t.col1, t.col2, tt.col2
from @t t
join @t tt on tt.id=(select max(id) from @t where col1='X' and id <= t.id)
[/code]

elsasoft.org

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-10 : 15:58:37

Jezemine's query should work just fine, but you could save a join:

select 
	t.id, t.col1, t.col2, (select MAX(col2) from @t where col1='X' and id <= t.id)
from @t t

NTC
Starting Member

21 Posts

Posted - 2007-12-11 : 21:39:11

I could make Field3 with this:
Select Field2 from TableName as A
Where A.Key=
(SELECT Max(Key) FROM TableName AS F
WHERE F.Field1="X" AND F.Key<=TableName.Key))

but it is real slow....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources