SQL query help to print like following with loop

Question

Hi All,

Some one help to print out put like follwowing...

1
11
111
1111
11111
22222
2222
222
22
2

Thanks,
Srinivas

Answer 1

Hope it helps ;)

DECLARE @VAR int
SELECT @VAR = 1
WHILE @VAR <= 5
BEGIN
PRINT replicate('1', @VAR )
SET @VAR = @VAR + 1
END
while @VAR >=1
begin
print replicate('2',@VAR )
set @VAR =@VAR -1
end

Answer 2

Hi，@Srinivas K

Welcome to Microsoft T-SQL Q&A Forum!

As you said, since the logic of the loop is clear, you only need to put the loop statement into the stored procedure for execution, call the stored procedure through exec, and give the variable printed by the loop.Please check this：

create procedure test @var int  
as  
WHILE @var <= 5   
BEGIN  
PRINT replicate('1', @var)  
SET @var = @var + 1  
END  
while @var>=1  
begin  
print replicate('2',@var-1)  
set @var=@var-1 ---1  
end  
  
exec test  1  

Here is the output：

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

select replicate(i,Case when i%2=1 then n else 5-n+1 end)
from
(select 1 i
union all
select 2
) t
cross apply (values(1),(2),(3),(4),(5)) d(n)

/*
(No column name)
1
11
111
1111
11111
22222
2222
222
22
2

*/

Answer 4

DECLARE @test AS TABLE (id int identity(1,1) PRIMARY KEY, val int,rep int);
INSERT INTO @test VALUES (1,5), (2,5)
--,(3,5),(4,5),(5,5) ;

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

select
--id,
replicate(val,Case when id%2=1 then n else rep-n+1 end) Result
--, replicate(val,n) Result2
from @test t , Nums
where n<=rep
order by id, n
/*
Result
1
11
111
1111
11111
22222
2222
222
22
2

*/