add column dynamically with select query

Question

I have one table which has 20 columns default

These 20 columns named as D1 D2 D3...D20 , now with select query i want to add the other columns dynamically.. for ex D21 D22...D31, so how can i write a query to add this columns dynamically with incremented value..max limit is 31,please help

default table columns
D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14 D15 D16 D17 D18 D19 D20

now i want to add columns in continuity to D20 i.e D21 and so on upto D31 , while selecting this columns the other column i.e fro D21 to D31 also get added and selected

query is

select * ,'  ' as D21 from tbl1

this will give me all 20 columns upto D20 and one additional D21 i want it upto D31 without hardcoding other column name

Answer 1

try below code

declare @a int =21
Declare @sql varchar(max)='select *,'
while @a <32
begin
set @sql = @sql + cast(@a as varchar) + ' as D' + cast(@a as varchar) + ' ,'

set @a = @a+1

end
set @sql = substring(@sql,0,len(@sql)-1) + ' from tbl1'

 exec( @sql)