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&gt;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...