How do I add an ascending count to a column of data via tsql as I am selecting it?

How do I add an ascending count to a column of data via tsql as I am selecting it? i.e

Column Data                Want to return

AAA                                 AAA1
BBB                                  BBB2
CCC                                  CCC3
DDD                                  DDD4

Guessing there is a way to concatenate the column data to row_number but haven't been able to work out the exact statement required.
ams_groupAsked:
Who is Participating?
 
mayank_joshiConnect With a Mentor Commented:
WITH CTE AS
(
    SELECT Coulmn_Data,   ROW_NUMBER() OVER (ORDER BY Coulmn_Data ) AS 'RowNumber'
    FROM Table1
) 
SELECT Coulmn_Data,Cast( Coulmn_Data as NVARCHAR(100)) +Cast(RowNumber  as NVARCHAR(100) ) as COL1 FROM CTE;

Open in new window

0
 
mayank_joshiCommented:
if ' Column Data' is non numeric you may  try:-

WITH CTE AS
(
    SELECT Coulmn_Data,   ROW_NUMBER() OVER (ORDER BY Coulmn_Data ) AS 'RowNumber'
    FROM Table1
) 
SELECT Coulmn_Data,Coulmn_Data +RowNumber as COL1 FROM CTE;

Open in new window

0
 
mayank_joshiCommented:
shorter:-

 
SELECT Coulmn_Data, Coulmn_Data+Cast( ROW_NUMBER() OVER (ORDER BY Coulmn_Data ) AS 
NVARCHAR(100)) 'RowNumber'
    FROM Table1

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ams_groupAuthor Commented:
Thanks, that was spot on
0
 
mayank_joshiCommented:
I VE TESETED  THIS.IT WORKS FINE:-

SELECT column_data, column_data+Cast( ROW_NUMBER() OVER (ORDER BY column_data ) AS 
NVARCHAR(100)) as Want_To_Return
    FROM testtbl

Open in new window

0
 
ams_groupAuthor Commented:
Thanks for your great solutions, just a quick question, any idea if it is possible to achieve a similar result in SQL 2000? Guessing identity could be a candiate for use although not sure it can be used to similar effect?
0
 
mayank_joshiCommented:
There is no direct equivalent to  rownum or row id in SQL Server 2000. Strictly speaking, in a relational database, rows within a table are not ordered and a row id won't really make sense. But if you need that functionality, consider the following three alternatives:

    * Add an IDENTITY column to your table. See Books Online for more information

    * Use the following query to generate a row number for each row. The following query generates a row number for each row in the authors table of pubs database. For this query to work, the table must have a unique key.

 
    SELECT        (SELECT COUNT(i.au_id) 
                     FROM pubs..authors i 
                     WHERE i.au_id >= o.au_id ) AS RowID, 
                    au_fname + ' ' + au_lname AS 'Author name'
      FROM          pubs..authors o
      ORDER BY      RowID

Open in new window


    * Use a temporary table approach, to store the entire resultset into a temporary table, along with a row id generated by the IDENTITY() function. Creating a temporary table will be costly, especially when you are working with large tables. Go for this approach, if you don't have a unique key in your table. Search for IDENTITY (Function) in SQL Server Books Online.

For more ideas on this topic, click http://support.microsoft.com/support/kb/articles/q186/1/33.asp  to read an informative article from Microsoft Knowledgebase.
0
All Courses

From novice to tech pro — start learning today.