asked on

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.

ASKER CERTIFIED SOLUTION

mayank_joshi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mayank_joshi

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

mayank_joshi

shorter:-

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

Open in new window

ams_group

ASKER

Thanks, that was spot on

mayank_joshi

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

ams_group

ASKER

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?

mayank_joshi

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.