|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
"Colescing" values in a field?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:
Show quote > Hello all, i am wondering if i can do the following with simple TSQL Is SQL Server a word processor or a DBMS? How about putting the values> 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. 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 -- Will wrote:
> I think you guys misread it, I interpreted 1,2,3,4,5,6...... as being Perhaps you are right. If Arthur had included DDL and some proper> 1 > 2 > 3 > 4 > 5 > 6 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: Show quote >I think you guy misread it, ... :-) "Will" <william_p***@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?
Another one would be... is this column being referenced by another table? I would lean towards doing this in a client application. Show quote "Arthur Dent" <hitchhikersguideto-n***@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. > > 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/  |
|||||||||||||||||||||||
Other interesting topics