TSQL: Howto add a char to a select statement.

Question

I have the following statement

SELECT id, descr from mytable

which returns

1, 'Test1'
4, 'Test4'
6, 'Test6'
8, 'Test8'
22, 'Test22'

Now I want the display to Add a sequential character to the first 4 results...

'A', 1, 'Test1'
'B', 4, 'Test4'
'C', 6, 'Test6'
'D', 8, 'Test8'
'',22, 'Test22'

Thoughts?

Edit: Would prefer a SQL Server 2000 example if possible.

Answer 1

SELECT CASE WHEN ROWNUMBER < 4
           THEN CHAR(65 + ROWNUMBER - 1)
           ELSE ''
       AS <WHATEVER>
       ,X.id
       ,X.descr
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY <WHATEVER>) AS ROWNUMBER
        ,id
        ,descr
    FROM mytable
) AS X

This in SQL Server 2005 and up.

In SQL Server 2000 (assuming id is your sort order and unique):

SELECT CASE WHEN rownumbers.rownumber < 4
           THEN CHAR(65 + rownumbers.rownumber - 1)
           ELSE ''
       AS <WHATEVER>
       ,mytable.id
       ,mytable.descr
FROM (
    SELECT l.id, COUNT(*) AS rownumber
    FROM mytable AS l
    LEFT JOIN mytable AS r
        ON l.id >= r.id
    GROUP BY l.id
) AS rownumbers
INNER JOIN mytable
    ON mytable.id = rownumbers.id

Answer 2

You can't do this in a single select statement in SQL Server 2000. SQL Server 2000 has no ROWNUM statement.

If you want to use a temp table you could do it with IDENTITY function.

SELECT IDENTITY(int,1,1) rownum, id, descr INTO #mytable from mytable

SELECT CASE WHEN ROWNUMBER < 4 THEN CHAR(65 + ROWNUMBER - 1) ELSE '' AS ,X.id ,X.descr FROM #mytable X ORDER BY rownum

NOTE: IDENTITY function can only be used in a SELECT INTO statement.