CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlcalculated-columns
OrElse
OrElse

Reputation: 9959

Creating a computed column in a View. Is it possible?

My SQL view returns the following

ID Name
AA Gina
AB George
AC John

I would like to add a computed column UpCounter so my view returns something like 

ID Name    UpCounter
AA Gina    1
AB George  2
AC John    3

Is it possible?

UPDATE: The UpCounter is actually the row index

Upvotes: 1

Views: 5074

Answers (2)

HardCode
HardCode

Reputation: 6756

You can use the ROW_NUMBER() function:

SELECT ROW_NUMBER() OVER (ORDER BY [ID]) AS MyIndex, 
       [ID], 
       [Name] 
FROM   MyTable

Also, note that a computed column is something different. A computed column is a "virtual column" on a table that just stores the forumla to be calculated then the field is selected. It does not take up row space.

Upvotes: 1

Joe Phillips
Joe Phillips

Reputation: 51100

See the OVER Clause.

SELECT ID, Name, ROW_NUMBER() OVER(ORDER BY ID ASC) AS UpCounter
FROM xyz

Here's a use case:

WITH x AS (
    SELECT 'AA' as ID, 'Gina' as Name
    UNION
    SELECT 'AB' as ID, 'George' as Name
    UNION
    SELECT 'AC' as ID, 'John' as Name
)
SELECT ID, Name, ROW_NUMBER() OVER(ORDER BY ID ASC) AS UpCounter
FROM x

Upvotes: 4

Related Questions