Link to home
Start Free TrialLog in
Avatar of paultran00
paultran00Flag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of paultran00

ASKER

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.)
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?
It should work.  Please post the view code so I can see what's wrong.
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

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.