How can I order one table by a field value (not a column) in another table?

I have a Customer table :

CustomerID	FirstName	LastName
1	Ben	Brown
2	Adam	Green

And I would like to order it based on another table TableInfo which contains the column names of the first table in rows and the order direction.

Source_Table	Source_Column	Order_By	Order_Of_Order_By
Customer	CustomerID	NULL	NULL
Customer	FirstName	ASC	1
Customer	LastName	NULL	NULL

The first table can be anything, the second table will always contain the same column headers, but the rows will depend on the first table.

I would like to do something like

select * from Customer
order by 
(select Source_Column  + ' '+ Order_By  from TableInfo where order_by is not NULL)

This doesn't work as I can't use this subquery as an Order by column name.

How can I make it work?

Solution

One possible solution is to use dynamic sql, along with a couple of built in sql-server items, the STUFF function and FOR XML PATH clause, and the EXECUTE command with built in stored procedure sp_executesql:

IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE table #customer (customerid int, firstname varchar(25), lastname varchar(25));
declare @sourcetables table (source_table varchar(50), source_column varchar(50), order_by varchar(5), order_of_order_by int)


insert into #customer
values(1, 'Ben', 'Brown'), (2, 'Adam', 'Green')

insert into @sourcetables
values('#customer', 'customerid', null, null), ('#customer', 'firstname', 'ASC', 1), ('#customer', 'lastname', null, null)

declare @sqlstatement nvarchar(max) = '';
--used as a parameter to choose which table we want to order and select from
--simulate a parameterized query
declare @tablename varchar(50) = '#customer';

--create the dynamic sql statement
--STUFF with FOR XML PATH Will flatten out the different
--column names you want to order by along with the appropriate
--order by type (ASC, DESC)
set @sqlstatement = 'SELECT * FROM ' + @tablename + ' ORDER BY ' +
STUFF((select ', ' + source_column + ' ' 
+ order_by from @sourcetables where [source_table] = @tablename 
and order_by is not null order by order_of_order_by 
FOR XML PATH ('')), 1, 1, '')

--This will show you the sql statement as a dynamic sql string
--select @sqlstatement

--This is used to execute the sql statement via built in sp
EXECUTE sp_executesql @sqlstatement;

IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

More about dynamic sql: dynamic-sql-in-sql-server