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: 397

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