Select columns dynamically from a while loop

Question

I am attempting to dynamically select my columns from a table (dbo.Ordinal) which lists every column and it's position. These column headings correspond to the table Y2.

DECLARE 
@ord int = 1,
@colname varchar(max),
@div varchar(max) = '',
@sql varchar (max)


WHILE @ord < 3 BEGIN
    SET @ord = @ord + 1

    Set @colname = '(SELECT BR FROM dbo.[Ordinal] WHERE ORDINAL_POSITION=' +CAST(@ord AS Varchar(10))+')'  

    SET @div = @div + ',' +(@colname)
END


Set @sql = '(SELECT Simulation' + @div + ' FROM dbo.[Y2 HR]) ORDER BY Simulation'


EXECUTE (@sql)

This code currently fills the resulting table with the column title I am trying to select. Ideally i'd like to see @div producing a text string opf the desired columns that can then be used in @sql,

Currently if I print @sql, the output is :

(SELECT Simulation,(SELECT BR FROM dbo.[Ordinal] WHERE ORDINAL_POSITION=2),(SELECT BR FROM dbo.[Ordinal] WHERE ORDINAL_POSITION=3) FROM dbo.[Y2 HR]) ORDER BY Simulation.

Any way to achieve this or suggestions for a different approach are welcome

Thanks

sql server? oracle ? mysql?

Fredou
– Fredou

2014-08-26 14:11:52 +00:00
Commented Aug 26, 2014 at 14:11 — Fredou
– Fredou, Commented Aug 26, 2014 at 14:11

Yanire Romero · Accepted Answer · 2014-08-26 14:17:15Z

4

You can do it much simpler:

DECLARE 
@ord int = 1,
@colname varchar(max),
@div varchar(max) = '',
@sql varchar (max)


WHILE @ord < 3 BEGIN
    SET @ord = @ord + 1

    SELECT @colname = BR FROM dbo.[Ordinal] WHERE ORDINAL_POSITION= @ord

    SET @div = @div + ',' +(@colname)
END


Set @sql = '(SELECT Simulation' + @div + ' FROM dbo.[Y2 HR]) ORDER BY Simulation'


EXECUTE (@sql)

answered Aug 26, 2014 at 14:17

Yanire Romero

4823 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select columns dynamically from a while loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related