How to arrange continuous serial number in to two or multiple column sequentially in sql server?

ecfsfe2w  于 2023-03-07  发布在  SQL Server
关注(0)|答案(5)|浏览(137)

I want to print or display 1 to 10 or any max number in two column format using MS Sql-Server query. Just like below attached screen shot image.

So please give any suggestion.

k2fxgqgv

k2fxgqgv1#

I like to use recursive queries for this:

with cte (num1, num2) as (
    select 1, 2 
    union all
    select num1 + 2, num2 + 2 from cte where num2 < 10
)
select * from cte order by num1

You control the maximum number with the inequality condition in the recursive member of the cte.

If you need to generate more than 100 rows, you need to add option(maxrecursion 0) at the very end of the query.

gudnpqoy

gudnpqoy2#

Using a couple of inline tallies would be way faster than a WHILE . This version will go up to 1000 integers (500 rows):

DECLARE @Start int = 1,
        @End int = 99;

SELECT TOP(CONVERT(int,CEILING(((@End*1.) - @Start + 1)/2)))
       (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1)*2 + @Start AS Number1,
       CASE WHEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1)*2 + @Start +1 <= @End THEN (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1)*2 + @Start +1 END AS Number2
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N1(N)
     CROSS APPLY (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N2(N)
     CROSS APPLY (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N3(N);

An alternative way that looks less messy with the CASE and TOP would be to use a couple of CTEs:

WITH Tally AS(
    SELECT TOP(@End - @Start + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 + @Start AS I
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N1(N)
         CROSS APPLY (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N2(N)
         CROSS APPLY (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N3(N)),
Numbers AS(
    SELECT I AS Number1,
           LEAD(I) OVER (ORDER BY I) AS Number2
    FROM Tally)
SELECT Number1,
       Number2
FROM Numbers 
WHERE Number1 % 2 = @Start % 2;
d6kp6zgx

d6kp6zgx3#

Assuming you are starting with a table with one column, you can use:

select min(number), max(number)
from sample_data
group by floor( (number - 1) / 2);
vcudknz3

vcudknz34#

Alternatively, set-based solution using window functions:

use tempdb

;with sample_data as (
select 1 as val union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 
) 

, sample_data_split  as
(
    select 
        val
    ,   2- row_number() over (order by val) % 2 as columnid
    ,  NTILE((select count(*) / 2  from sample_data) ) over (order by val) groupid
    from sample_data
)

the intermediate result of sample_data_split is:

val columnid    groupid
1   1   1
2   2   1
3   1   2
4   2   2
5   1   3
6   2   3
7   1   4
8   2   4
9   1   5
10  2   5

and then to get the resultset into a desired format:

select 
    min(case when columnid = 1 then val end) as column1
,   min(case when columnid = 2 then val end) as column2
from sample_data_split
group by groupid
column1 column2
1   2
3   4
5   6
7   8
9   10

Those CTEs can be merged into a single SELECT:

select 
    min(case when columnid = 1 then val end) as column1
,   min(case when columnid = 2 then val end) as column2
from 
(
    select 
        val
    ,   2- row_number() over (order by val) % 2 as columnid
    ,  NTILE((select count(*) / 2  from sample_data) ) over (order by val) groupid
    from sample_data
) d
group by groupid

The positive side of a such approach, that it scales well and has no upper boundary on how much rows to be processed

uurv41yg

uurv41yg5#

So I got this solution on it as below...

declare @t table
(
id int identity(1,1),
Number_1 int,
Number_2 int
)

declare @min int=1
declare @max int=10
declare @a int=0;
declare @id int=0

while(@min<=@max)
begin

if(@a=0)
begin
insert into @t
select @min,null
set @a=1
end
else if(@a=1)
begin
select top 1 @id=id from @t order by id desc
update @t set Number_2=@min where id=@id
set @a=0
end

set @min=@min+1
end

select Number_1,Number_2 from @t

相关问题