Generating Alphabet Strings in SQL Server

Question

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

Suppose I have 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 get the following result.

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

How do I create "a)", "b)",...? I am not allowed to add an extra temporary table or table variable to store alphabets initially. They must be generated.

And that should be done in SQL Server 2005.

Any thoughts?

thank

Famously cheran 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 row_number(), . , , , .

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