The Excel Column Name assigment problem
Posted
by Peter Larsson
on SQL Team
See other posts from SQL Team
or by Peter Larsson
Published on Wed, 28 Apr 2010 13:45:14 GMT
Indexed on
2010/04/28
13:54 UTC
Read the original article
Hit count: 363
Filed under:
Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.
DECLARE @Value INT = 8839,
@Base TINYINT = 26
;WITH cteSequence(Value, Delta, Quote, Base, Chr)
AS (
SELECT CAST(@Value AS INT) AS Value,
CAST(1 AS INT) AS Delta,
CAST(@Base AS INT) AS Quote,
CAST(@Base AS INT) AS Base,
CHAR(65 +(@Value - 1) % @Base) AS Chr
UNION ALL
SELECT Value AS Value,
Quote AS Delta,
26 * Quote AS Quote,
Base AS Base,
CHAR(65 +((Value - Delta)/ Quote - 1) % Base) AS Chr
FROM cteSequence
WHERE CHAR(65 +((Value - Delta)/ Quote - 1) % Base) <> '@'
)
SELECT CAST(Msg AS VARCHAR(MAX))
FROM (
SELECT '' + Chr
FROM cteSequence
ORDER BY Delta DESC
FOR XML PATH('')
) AS x(Msg)
© SQL Team or respective owner
