Select columns dynamically from sql server table

Lets say I have a table that actually store record which looks like

Create Table dbo.Info
(
 SN int primary key identity(1,1),
 FirstName nvarchar(50) not null,
 LastName nvarchar(50) not null,
 Gender char(1) default 'M',
 Age int check(Age>0)
)

Now for reporting purpose I only need this table column name and save in another table

Create Table Report.InfoColumnOrder
(
 SN int primary key identity(1,1),
 UserId int,
 ColumnName nvarchar(100)
)

thus record looks like

1, 1, FirstName
2, 1, LastName
3, 1, Gender
4, 2, LastName
5, 2, FirstName
6, 2, Age
7, 2, Gender

Now when user with id 1 login and wants report from table Info
select command should look like

Select FirstName, LastName, Gender from dbo.Info

whereas for a user with id 2 select command will be

Select LastName, FirstName, Age, Gender from dbo.Info

Since, user can order the column from client side select statement should be dynamic. Is it possible?

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks.

DECLARE @qry NVARCHAR(MAX) =
'SELECT ' + STUFF((
    SELECT ', ' + QUOTENAME(c.name) 
    FROM Report.InfoColumnOrder r
    JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info')
    WHERE u.UserId = @user
    ORDER BY r.SN
    FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'') +
 ' FROM dbo.Info'
;
EXEC sp_executesql @qry, N'@user int', @user = @userid;

Or something like that - I'm writing this on my phone...

StackExchange Database Administrators Q#107424, answer score: 5