In TSQL, How do I create a column in my view so that it display a number on each row that gets incremented?

TSQL: SQL SERVER 2008R2

Hi,

How do I create a column in my view so that it display a number on each row that gets incremented?

For example:

data of students:

LAST NAME, FIRSTNAME

white, snow
charming, prince
mouse, mickey

I want this:

LINE #, LAST NAME, FIRST NAME

1, white, snow
2, charming, prince
3, mouse, mickey

It's the first field named Line_No, which is blank instead of each line getting an incremented number 1, 2, 3, etc.

VIEW:

SELECT     '' AS Line_No, '' AS REJECTED, DOCNUM AS [External Claim ID], RXCLAIMNUM, MEMBER_ID, DOF, RX_NUMBER, QTY, DS, NCPDP, 
                      RX_NETWORK_ID AS [RX Network ID], PRICING_TERM_DESC AS [Pricing Term Used for Claim], '' AS [Pharmacy AWP Discount Rate], 
                      '' AS [Pharmacy Dispensing Fee], TOTAL_TAXED_LOE_AMOUNT AS [Total Taxed/LOE Amount], UNIT_PRICE_OF_AWP AS [Unit Price of AWP], '' AS [Unit Price of MAC], 
                      '' AS [Ingredient Cost at AWP Discount], '' AS [Ingredient Cost at MAC], '' AS [Claim adjudicate using Lesser of logic], CLAIM_DISCOUNT_RATE AS [Claim Discount Rate],
                       CLAIM_DISPENSING_FEE AS [Claim Dispensing Fee], '' AS [Did Claim Adjudicate with Corect Discount/Disp Fee], '' AS [Additional Pricing review], 
                      FORMULARY_COMPLIANCE_CODE AS [Formulary Compliance Code], '' AS [Expected Copay], '' AS [Copay for Claim], '' AS [Copay Comment], 
                      BRAND_GENERIC_TRADEMARK AS [Brand / Generic Description], 
                      CLAIM_OVERRIDE_REASON_CODE1 + ' ' + CLAIM_OVERRIDE_REASON_CODE2 + ' ' + CLAIM_OVERRIDE_REASON_CODE3 + ' ' + CLAIM_OVERRIDE_REASON_CODE4 +
                       ' ' + CLAIM_OVERRIDE_REASON_CODE5 + ' ' + CLAIM_OVERRIDE_REASON_CODE6 AS [Claim Override Reason Code], '' AS [Claim Reject Code], 
                      '' AS [Claim Reject Reason Code]
FROM         dbo.tbl_FEP_PCL

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 73

Scott PletcherSenior DBACommented: 2018-08-31

No guarantee of line# order, but you can do this:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [LINE #], [LAST NAME], [FIRST NAME]
FROM ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

paultran00Author Commented: 2018-08-31

Thank you.

Naturally you can specify [LAST NAME], [FIRST NAME] or other specific column(s) in the ORDER BY if you want that order for the line#s.

If you want the "order the rows were inserted", there's no way to guarantee that unless you have a datetime in the table that reflects that, and you can sort on that column.

(Sorry, I was busy earlier and didn't have time then to add this extra description.)

paultran00Author Commented: 2018-09-06

Actually, your solution worked as a query but not as a VIEW, which is what I need. Do you know how to make it work in as a VIEW?

Scott PletcherSenior DBACommented: 2018-09-06

It should work. Please post the view code so I can see what's wrong.

Hmm, you're selecting '', try using row_number instead:

VIEW:

SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Line_No,
...

OK, it does work as a view but I had to click "ignore " about "SQL text cannot be represented in the grid pane and diagram pane. " and "the query cannot be represented graphically in the diagram and criteria Pane".

Thank you very much.

OK, interesting. I've never used the gui to create a view so I've never seen that issue.

In TSQL, How do I create a column in my view so that it display a number on each row that gets incremented?

Who is Participating?