How to write sql query to get the outout?

Question

My output should be in this way:

1

1 2

1 2 3

1 2 3 4

Thanks in Advance.

Could anyone please look into this and do needful?

Answer 1

Hi @Ramana Kopparapu,

Please try the following solution. It is using T-SQL and its XQuery.

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT PRIMARY KEY);
INSERT @tbl (ID) VALUES
(1), (2), (3), (4), (5);
-- DDL and sample data population, end

SELECT * 
	, CAST(N'' AS XML).query('
		for $i in (1, 2, 3, 4, 5) 
		return if ($i le sql:column("ID")) then $i else ()
	').value('.', 'VARCHAR(100)') AS result
FROM @tbl;

Output

ID	result
1	1
2	1 2
3	1 2 3
4	1 2 3 4
5	1 2 3 4 5

Answer 2

Here is a query without XML, but which requires SQL 2022:

SELECT value, 
       (SELECT string_agg(convert(varchar, b.value), ' ') WITHIN GROUP (ORDER BY b.value)
        FROM   generate_series(1, a.value) b)
FROM   generate_series(1, 5) a