A three-column table row from a column table

Question

I have a noobie question:

I need to write an SQL script that returns 3 columns retrieved from a single @T column @T with integer field N, for example:

 declare @t table(n int) insert into @t(n) values (3),(4),(5), (6),(7),(8), (9),(10),(11), (13),(14),(15), (16),(17),(18), (19) 

The 3-column result table should contain data from the @T table, @T by row. If @T no missing values ​​in the @T table (for example, {2,4} 3), then the corresponding cell in the result set must be NULL .

Example 1:
For a table with {2,3,4,5} values, the results table should look like this:

 NULL 2 3 4 5 NULL 

Example 2: For a table with values {2,4} results table should look like this:

 NULL 2 NULL 4 NULL NULL 

Answer 1

The table of numbers can be used as follows:

 select T1.n, T2.n, T3.n from Numbers as N left outer join @T as T1 on N.Number * 3 + 1 = T1.n left outer join @T as T2 on N.Number * 3 + 2 = T2.n left outer join @T as T3 on N.Number * 3 + 3 = T3.n where N.Number between 0 and (select max(n) from @T) / 3 

A working example using master..spt_values as a table of numbers.

 declare @t table(n int) insert into @t(n) values (3),(4),(5), (6),(7),(8), (9),(10),(11), (13),(14),(15), (16),(17),(18), (19) ;with Numbers(Number) as ( select number from master..spt_values where type = 'P' ) select T1.n, T2.n, T3.n from Numbers as N left outer join @T as T1 on N.Number * 3 + 1 = T1.n left outer join @T as T2 on N.Number * 3 + 2 = T2.n left outer join @T as T3 on N.Number * 3 + 3 = T3.n where N.Number between 0 and (select max(n) from @T) / 3 

Or you can use a summary table of numbers:

 select [1], [2], [3] from ( select (N.Number - 1) / 3 as Number, Tn, 1 + ((N.Number - 1) % 3) as rn from Numbers as N left outer join @T as T on N.Number = Tn where N.Number between 1 and (select max(n) from @T) ) as T pivot (min(n) for rn in ([1], [2], [3])) as P