How to pass a column name using string_split - ms sql server?
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)