Wednesday, 21 August 2013

How to Increment a nvarchar value + number when more then one value is displayed for a given ID

How to Increment a nvarchar value + number when more then one value is
displayed for a given ID

I need to add a address type value to a column when more then one value is
returned from the query below. For example if a single result is returned
then I want the value in the address type column to be Business. But if
there is more then one value returned, I want it to increment a value
after the first result to be Alternate Business 1,Alternate Business 2,
Alternate Business 3 etc.
Can anyone help me out?
SELECT al.ADDRESS_ID, addr.LINE1 + ' (' + addr.LABEL + ')' AS
ModifiedLine1, addr2.ADDR_TYP_ID, addr2.LABEL, addr2.LINE2, addr2.LINE3,
addr2.CITY, addr2.STATE, addr2.COUNTRY, addr2.POSTAL_CD FROM
INT_AUX_LST_ADDR al LEFT JOIN INT_AUX_ADDRESS addr ON addr.ADDRESS_ID =
al.ADDRESS_ID LEFT JOIN INT_AUX_ADDRESS addr2 ON addr2.ADDRESS_ID =
al.ADDRESS_ID LEFT JOIN INT_RELATION_TYP rt ON rt.RLTN_TYP_ID =
al.RLTN_TYP_ID WHERE al.LISTING_ID = 1

No comments:

Post a Comment