SQL Server Table Value Constructor with identity column

dgtucam1 于 2023-05-28 发布在其他

关注(0)|答案(1)|浏览(127)

In SQL Server how can I add a ordinal/identity/row number to this result set?

select * 
from (VALUES('c'), ('a'), ('b')) t(value)

value
c
a
b

So it returns the same as this:

select * 
from STRING_SPLIT('c,a,b', ',', 1) t

value	ordinal
c	1
a	2
b	3

I don't want to just do VALUES(1,'c'),(2,'a'),(3,'b') because there will be a long list that will change from time to time and can't have gaps in the ordinal.

sql-server

来源：https://stackoverflow.com/questions/76337518/table-value-constructor-with-identity-column

1条答案

按热度按时间

g6ll5ycj1#

You can't do it using the values constructor, the closest you can get is to use a table variable with an identity column e.g.

declare @Test table ([Value] varchar(32), Ordinal int identity (1,1));

insert into @Test ([Value])
values ('c'),('a'),('b');

select *
from @Test
order by Ordinal;

Returns

Value	Ordinal
c	1
a	2
b	3

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server Table Value Constructor with identity column

1条答案

相关问题

热门标签

最新问答