CODEWITHSUNDEEP
sqlsql-server-2008
jcvegan
jcvegan

Reputation: 3170

Sql view with column identifier

I am creating a select query with union of three tables.... like this

select a as A,b as B c as C where c = x union
select b as A,d as B e as C where e = y and d = a union
select f as A,g as B,h as C

and the result of query is like this:

A   B   C
===========
1   abc ...
55  def ...
1   sas ...

so I want to have a column that count the number of row, just to prevent the repetition of identifier. Somthing like this

Row  A   B   C
================
1    1   abc ...
2    55  def ...
3    1   sas ...

....

My question is how it can be done?

Upvotes: 0

Views: 83

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

CREATE VIEW dbo.vname
AS
    SELECT [Row] = ROW_NUMBER() OVER (ORDER BY A), A, B, C FROM
    ( <UNION query here> ) AS x;

Replace ORDER BY A with whatever ordering you'd like to see applied. Note that you will need to use ORDER BY on the outer query against dbo.viewname to guarantee that Row will come out in that order.

Upvotes: 3

tobias86
tobias86

Reputation: 5029

You can use a common table expression to achieve this:

WITH unionTable
AS
(
    select a as A, b as B, c as C where c = x union
    select b as A, d as B, e as C where e = y and d = a union
    select f as A, g as B, h as C
)
SELECT ROW_NUMBER() OVER (ORDER BY A) AS RowNumber, * 
FROM unionTable

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147374

You can use ROW_NUMBER() like this:

SELECT ROW_NUMBER() OVER (ORDER BY A,B,C) AS RowNo, *
FROM
(
select a as A,b as B c as C where c = x 
union
select b as A,d as B e as C where e = y and d = a 
union
select f as A,g as B,h as C
) x

Upvotes: 5

Related Questions