Print Pyramid in sqlserver

Davin Martyn

9y2.6k

Hi Team,

I want to print infinte number pyramid in sqlserver like

abab

ababab

and after print i want to reverse like

baba

bababa

babababa using CTE.

Please suggest how can do this.

My Second Question :

Hi Team,

I have one table and inside table two columns id , value

id value

1 a

1 b

1 c

1 d

2 a

2 b

I want to ouput like

id Value

1 a,b,c,d

2 a,b

please write query for above question.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
Accepted answer
Regarding 1st question follow below link:

http://shrikantkhode.blogspot.in/2015/03/write-program-in-sql-server-to-print.html

http://mssqltune.blogspot.in/2015/03/write-program-in-sql-server-to-print.html

http://stackoverflow.com/questions/9930364/how-do-i-print-a-triangle-of-stars-using-sql

Regarding 2nd question, you need to use string aggregation:

http://stackoverflow.com/questions/15477743/listagg-in-sqlserver/19348687?noredirect=1#comment73313772_19348687

https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server
Davin Martyn9y ago
Hi Manas ,

Thanks very much.
I want to one information regarding last query.

I want to print with the help of cte so suggest to me how can do this if possible.
Dharmraj Thakur9y ago
Here is your solution....
--1st query
DECLARE @starLength int
SET @starLength=10 -- here you put any number of length
;With CTE(star)
AS
(
    SELECT CAST('ab' as varchar(max)) as star
        UNION ALL
    SELECT CAST(star +'ab' as varchar(max)) FROM CTE
        WHERE LEN(star)<@starLength
)
SELECT * FROM CTE
--2nd query
DECLARE @starLength int
SET @starLength=10 -- here you put any number of length
;With CTE(star)
AS
(
    SELECT CAST('ba' as varchar(max)) as star
        UNION ALL
    SELECT CAST(star +'ba' as varchar(max)) FROM CTE
        WHERE LEN(star)<@starLength
)
SELECT * FROM CTE
--third query
select ID,
    STUFF (
            (select ',' + CONVERT(varchar(10), Value) from table1 FOR XML PATH(''), TYPE )
            .value('.','NVARCHAR(MAX)'), 1, 1, ''
        ) as CommaSaperatedValues
from table1 group by ID
Davin Martyn9y ago
i have no idea how to do this in sql. i am on learning stage
Suvendu Shekhar Giri9y ago
Unfortunately, we can not help in your homework. Please ask your class teacher for the help.

In case you have very specific question on what you already tried and not working, we may help a bit though.