This script converts a numeric column index to an Excel column name (ex: AC). The script doesn't use cursors or any other iteration. It converts the value using only set-based code so it is very suitable for using within a function.
The @ColumnIndex parameter is 1-based (1 = column A)
DECLARE @ColumnIndex INT = 53 ; WITH N26 AS ( SELECTTOP 26 N = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM master.sys.columns A ) SELECT CASE WHEN N1.N = 1 THEN N'' ELSE NCHAR(UNICODE(N'A') + N1.N - 2) END + NCHAR(UNICODE(N'A') + N2.N - 1) FROM N26 N1 CROSS JOIN N26 N2 WHERE@ColumnIndex = ((N1.N - 1) * 26) + N2.N ORDERBY N1.N, N2.N
What native SQL Server options are available to export to Excel? Jeremy Kadlec explains.
2017-06-30
5,930 reads
This article demonstrates using SQL Server's data mining capabilities with the Excel.
2011-02-11
5,472 reads
Have you ever been required to create a database with nothing but a specification in Excel?
2009-11-12
7,637 reads
There is value in using the extended properties across some of the industry leading data modeling tools. What if I do not have access to any of those tools? Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services?
2008-12-09
4,930 reads
2010-02-19 (first published: 2008-12-05)
38,632 reads
