Results from query values in one column

Question

I am just curios about something I've never come across in sql server before.

This query:

SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(N)

gives me result:

+---+
| N |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

What is the rule here? Obviously this is aligning all values into one column. Is sql server's grammar that defines this with T(N)?

On the other side, this query gives results by separate columns:

select 0,1,2,3,4,5,6,7,8,9

I just don't understand why results from the first query aligned all into one column?

Indeed :) I am studying your CTE magic from here http://stackoverflow.com/a/42797191/867703 — FrenkyB, Mar 18 '17 at 13:42

score 3 · Answer 1 · answered Mar 18 '17 at 13:34

The values clause is similar what you can use in the insert statement, and it's called Table Value Constructor. Your example has only one column and several rows, but you can also have multiple columns separated by comma. The T(N) define you the alias name for the table (T) and name for the column (N).

SqlZim · Accepted Answer · 2017-03-18T13:59:16.390

James Z is right on the money, but to expand on what it does in the answer you were referencing:

In the code that is pulled from, that section is used to start numbers table for a stacked cte. The numbers themselves don't matter, but I like them like that. They could all be 1, or 0, it would not change how it is used in this instance.

Basically we have 10 rows, and then we are going to cross join it to self N number of times to increase the row count until as many or more than we need. In the cross join I alias n with the resulting amount of rows deka is 10, hecto is 100, kilo is 1,000, et cetera.

Here is a similar query outside of the function that you were referencing:

declare @fromdate date = '20000101';
declare @years    int  = 30;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo 
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

The stacked cte is very efficient for generating or simulating a numbers or dates table, though using an actual numbers or calendar table will perform better as the scale increases.

Check these out for related benchmarks:

In hist articles, Aaron Bertrand creates a stacked cte using

;WITH e1(n) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b),
....

Results from query values in one column

2 Answers2