Alphabet Series Generation in SQL Server

Question

Hi Team,

I am trying to generate a series of alphabets, using SQL Server.

Suppose I have a tables as follows

DECLARE @Funding TABLE (FundingDetailID INT,FundingID INT, DetailDescription VARCHAR(50))
INSERT INTO @Funding (FundingDetailID ,FundingID , DetailDescription) VALUES (1,107,'Desc 1')
INSERT INTO @Funding (FundingDetailID ,FundingID , DetailDescription) VALUES (1,107,'Desc 2')
INSERT INTO @Funding (FundingDetailID ,FundingID , DetailDescription) VALUES (1,107,'Desc 3')

I am trying to obtain the following result.

a) Desc 1
b) Desc 2
c) Desc 3

How do I generate “a)”, “b)”, … ? I am not allowed to add any extra temp table or table variable for storing the alphabets initially. They should be generated.

And this is to be done in SQL Server 2005.

Any thoughts ?

Thanks

Lijo Cheeran Joseph

Answer 1

Use ROW_NUMBER() as follows

DECLARE @Funding TABLE (FundingDetailID INT,FundingID INT, DetailDescription VARCHAR(50))
INSERT INTO @Funding VALUES (1,107,'Desc 1')
INSERT INTO @Funding VALUES (1,107,'Desc 2')
INSERT INTO @Funding VALUES (1,107,'Desc 3')

SELECT CHAR (CAST (96+ROW_NUMBER() OVER (Order BY FundingDetailID) AS VARCHAR)) + ') ' + DetailDescription
FROM @Funding



-----------------------------------------------------
a) Desc 1
b) Desc 2
c) Desc 3

Answer 2

Raj More already posted the row_number() while I was writing it. I voted for his answer, but here's my variant which does an update on the table, just in case you are interested in that.

update  f1
set     f1.DetailDescription = char(96 + f2.rn) + ') ' + f2.DetailDescription
from    @Funding f1
join    (
        select  row_number() over (order by FundingDetailId, 
                                      FundingId, DetailDescription) as rn
        ,       *
        from    @Funding f
        ) f2
on      f1.FundingDetailID = f2.FundingDetailID
        and f1.FundingID = f2.FundingID
        and f1.DetailDescription = f2.DetailDescription

select  *
from    @Funding