Generate sequential number in SQL - not by using Identity

Question

I am working on a task where my query will produce a fixed width column. One of the fields in the fixed width column needs to be a sequentially generated number.

Below is my query:

select
_row_ord = 40,
_cid = t.client_num,
_segment = 'ABC',
_value = 
concat(
'ABC*',
'XX**', --Hierarchical ID number-this field should be sequentially generated
'20*',
'1*','~'
    )
from #temp1 t

My output:

Is there a way to declare @num as a parameter that generates number sequentially?

PS: The fields inside the CONCAT function is all hardcoded. Only the 'XX' i.e., the sequential number has to be dynamically generated

Any help?!

I would ask why do you want this? Smashing sequential values inside a string is often a sign that you are not normalizing your data correctly. It is almost an indication of violating 1NF by stuffing multiple values into a single tuple. And how are you going to sort this correctly? And how are you going to handle when you run out of numbers because it looks like you want length of 2? What happens when you hit 100? — Sean Lange
– Sean Lange, Commented Jan 8, 2019 at 19:28
Do you have any maximum limit for this sequentially generated number part ?Is that below 100? — Anusha Subashini
– Anusha Subashini, Commented Jan 8, 2019 at 19:31
What version of SQL Server are you using? SELECT @@VERSION; — Eric Brandt
– Eric Brandt, Commented Jan 8, 2019 at 19:31
I am not having any limit for the sequential number generated. — Rick
– Rick, Commented Jan 8, 2019 at 19:34

Eric Brandt · Accepted Answer · 2019-01-08 19:47:40Z

3

You could create a SEQUENCE object, then call the NEXT VALUE FOR the SEQUENCE in your query.

Something along these lines:

CREATE SEQUENCE dbo.ExportValues
    START WITH 1  
    INCREMENT BY 1 ;  
GO

And then:

select
_row_ord = 40,
_cid = t.client_num,
_segment = 'ABC',
_value = 
concat(
'ABC*',
RIGHT(CONCAT('000000000000000', NEXT VALUE FOR dbo.ExportValues),15)
'**', 
'20*',
'1*','~'
    )
from #temp1 t

You'd have to tweak how many zeros there are for the padding and how many digits to trim it to for your requirements. If duplicate values are ok, you could have the SEQUENCE reset periodically. See the documentation for more on that. It's just another line in the CREATE statement.

answered Jan 8, 2019 at 19:47

Eric Brandt

8,1313 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rick Over a year ago

Thanks Eric. This is helpful

Eric Brandt Over a year ago

Glad it was useful. Thanks.

Gordon Linoff · Accepted Answer · 2019-01-08 19:17:35Z

1

You can use row_number() -- made a little more complicated because you are zero-padding it:

select _row_ord = 40, _cid = t.client_num, _segment = 'ABC',
       _value = concat('ABC*',
                       right('00' + convert(varchar(255), row_number() over (order by ?)), 2),
'XX**', --Hierarchical ID number-this field should be sequentially generated
                       '20*',
                       '1*','~'
                       )
from #temp1 t;

Note that the ? is for the column that specifies the ordering. If you don't care about the ordering of the numbers, use (select null) in place of the ?.

answered Jan 8, 2019 at 19:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

nicomp Over a year ago

row_number can change between runs of the query as rows are added/removed from the table.

Anusha Subashini Over a year ago

@Gordon Linoff your answer only work below 100 of the sequential number

Gordon Linoff Over a year ago

@AnushaSubashini . . . It wasn't me. The OP only has two digits for the number.

Collectives™ on Stack Overflow

Generate sequential number in SQL - not by using Identity

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related