字母序列生成的SQL Server
我试图生成一系列字母的,使用SQL Server。字母序列生成的SQL Server
假设我有一个表如下
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')
我试图得到下面的结果。
a) Desc 1
b) Desc 2
c) Desc 3
如何生成“a)”, “b)”,
...?我不允许添加任何额外的临时表或表变量来存储最初的字母。它们应该被生成。
这是要在SQL Server 2005中完成的。
有什么想法?
感谢
Lijo Cheeran约瑟夫
使用ROW_NUMBER()如下
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
+1:诅咒 - 你偷走了我的答案! :) – 2010-07-08 18:41:29
谢谢。以下也将工作,对吧? SELECT CHAR(CAST(96 + ROW_NUMBER()OVER(ORDER BY DetailDescription)AS VARCHAR))+ ')' + DetailDescription FROM @Funding – Lijo 2010-07-08 18:48:07
拉吉更已经发布的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
这是功课?如果是这样,请将其标记为 – 2010-07-08 18:32:16
是否仅限于26个项目? – kennytm 2010-07-08 18:35:56
是的,它仅限于26 – Lijo 2010-07-08 18:43:32