"Colescing" values in a field?

Hello all, i am wondering if i can do the following with simple TSQL
statements without needing to resort to using cursors... i am having a hard
time trying to think of a way, but TSQL isnt my 'native' sql programming
language (i 'grew up' on PL/SQL), so im hoping someone else can think of
something of how to do this.
I have a field which has essentially sequential values, say,
1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
(they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
records get added and deleted, and the sequnce winds up with holes in it...
1A, 1B, 1D, 1G, ...
Is there a slick or elegent T/SQL statement i can run to coalesce these
values back down to fill in the gaps?
So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
1C, 1D.
Thanks in advance.
- Arthur Dent.one obvious way, but perhaps not very elegant would be to insert the
values into a temporary table with an extra identity field, then use
that to remap your values with an update statement|||Arthur Dent wrote:
> Hello all, i am wondering if i can do the following with simple TSQL
> statements without needing to resort to using cursors... i am having a ha
rd
> time trying to think of a way, but TSQL isnt my 'native' sql programming
> language (i 'grew up' on PL/SQL), so im hoping someone else can think of
> something of how to do this.
> I have a field which has essentially sequential values, say,
> 1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
> (they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
> records get added and deleted, and the sequnce winds up with holes in it..
.
> 1A, 1B, 1D, 1G, ...
> Is there a slick or elegent T/SQL statement i can run to coalesce these
> values back down to fill in the gaps?
> So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
> 1C, 1D.
> Thanks in advance.
> - Arthur Dent.
Is SQL Server a word processor or a DBMS? How about putting the values
in a table rather than delimiting them in a column?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||One question comes to mind... WHY?
Another one would be... is this column being referenced by another table?
I would lean towards doing this in a client application.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eJvDfhYWGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Hello all, i am wondering if i can do the following with simple TSQL
> statements without needing to resort to using cursors... i am having a
> hard time trying to think of a way, but TSQL isnt my 'native' sql
> programming language (i 'grew up' on PL/SQL), so im hoping someone else
> can think of something of how to do this.
> I have a field which has essentially sequential values, say,
> 1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
> (they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
> records get added and deleted, and the sequnce winds up with holes in
> it...
> 1A, 1B, 1D, 1G, ...
> Is there a slick or elegent T/SQL statement i can run to coalesce these
> values back down to fill in the gaps?
> So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
> 1C, 1D.
> Thanks in advance.
> - Arthur Dent.
>|||I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
1
2
3
4
5
6|||Will wrote:
> I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
> 1
> 2
> 3
> 4
> 5
> 6
Perhaps you are right. If Arthur had included DDL and some proper
sample data then we wouldn't have to resort to guessing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I understood it the same way you did Will.
So you should change your post to:
>I think you guy misread it, ... :-)
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144336766.712050.287240@.j33g2000cwa.googlegroups.com...
>I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
> 1
> 2
> 3
> 4
> 5
> 6
>|||> One question comes to mind... WHY?
Obviously to make the result set pretty as must have been the intention of
whoever invented this lovely key generator. :)
ML
http://milambda.blogspot.com/