sqlsql-server

create a list of the alphabet via SQL

I would like to produce results from the alphabet via SQL?

Something like this:

A
B
C
D
E
F

I have tried:

SELECT 
'A','B','C'

But this just produces the letters across in columns.

Solution 
  • --
-- tally = 9466 rows in my db, select upper & lower alphas
-- 
;
with 
cte_tally as
(
select row_number() over (order by (select 1)) as n 
from sys.all_columns
)
select 
  char(n) as alpha
from 
  cte_tally
where
  (n > 64 and n < 91) or
  (n > 96 and n < 123);
go

    The sys.all_columns is a documented table. It will be around for a while.

    http://technet.microsoft.com/en-us/library/ms177522.aspx

    It seems clear that the table, sp_values, is undocumented and can be removed in the future without any comment from Microsoft.