SQL ServerSelect

SQL Server - Select - Creating an output with Row Numbers

SQL Server provides a row_number() function.

Here is an example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with DistinctDescriptions as (
      select description
      from [dbo].[20260427 SacSewer Fleet Information_Collections] g
      where g.description is not null
      union
      select description
      from [dbo].[20260427 SacSewer Fleet Information_Treatment] g
      where g.description is not null
)
Select      AssetClassID = null,
            ClassCode='a'+cast(Row_Number() over (order by description) + 100 as varchar(3)) ,
            ClassDesc= description
from DistinctDescriptions
go