How to pass a column name using string_split - ms sql server? I'm trying to pass column names using a string split because I need the values from that table to join with another table. 

I've already achieved this in Postgresql where I get the values from these columns:

   SELECT unnest(array["column_name1","column_name2","column_name3" AS "amount"
   FROM table1


The unnest in combination with the quotes around the column names returns an array to a set of row for every value. 

Amount
307579.00
25579.00
212871.00


Now I want to do the same in MS Server:

   DECLARE @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
   SELECT "value" from string_split(@amount,'/')


But this only gives me the literal string:

Amount
column_name1
column_name2
column_name3


I've been trying and searching how to pass an elemant that will read as a column name and shows the values in one column like the first example, maybe I shouldn't use the string splitter etc. Kinda stuck on this..
You'll need to use dynamic SQL to implement your logic, by constructing a whole new SELECT query.

For example:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' FROM table1'
PRINT @CMD
EXEC(@CMD)


Its output would be:

SELECT [column_name1], [column_name2], [column_name3] FROM table1


In order to get all the values under a single column, you'll need to "UnPivot" the data. There are two main ways to do so. One is by a standard UNPIVOT command, like so:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + ', ', N'SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( ') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N' ) AS unpvt'
PRINT @CMD
EXEC(@CMD)


Its output would be:

SELECT unpvt.* FROM table1 UNPIVOT (amount FOR amounttype IN ( [column_name1], [column_name2], [column_name3]) AS unpvt


Another way is to use CROSS APPLY with a Values Constructor, like this:

DECLARE @CMD nvarchar(max), @amount NVARCHAR(50) = 'column_name1/column_name2/column_name3'
SELECT @CMD = ISNULL(@CMD + '), (', N'SELECT v.amount FROM table1 CROSS APPLY(VALUES(') + QUOTENAME([value])
FROM STRING_SPLIT(@amount,'/')
SET @CMD = @CMD + N')) AS v(amount)'
PRINT @CMD
EXEC(@CMD)


Its output would be:

SELECT v.amount FROM table1 CROSS APPLY(VALUES([column_name1]), ([column_name2]), ([column_name3])) AS v(amount)