Select columns dynamically from sql server table

Question

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?

Answer 1

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...