How to pass a column name using string_split - ms sql server?

Question

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

• Martin Smith: For those of us unfamiliar with Postgresql what does the unnest(array do? Can you provide example data and desired results?
• McNets: string_split starts at SqlServer 2016, you should use some function like stackoverflow.com/a/10914602/3270427.
• sql_mind: Yes, so I thought, but I've been using string_split in SqlServer 2014 and works just fine when I actually need the literal string.
• McNets: Then someone has added a function with this name.

Answer 1

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)

• sql_mind: This actually gives me the values, but in separate columns. Is it possible to have one column e.g. 'amount' with the values stored there?
• Eitan Blumin: Seems I misunderstood your requirements. Sure, I'll edit my answer accordingly.
• sql_mind: I used the CROSS APPLY and got my desired result. Thanks!
• Eitan Blumin: Pleasure @MarG :)

Answer 2

create table t (id int, mytext varchar(500));
insert into t values (1, 'column_name1/column_name2/column_name3');
select id, value as amount
from t
cross apply STRING_SPLIT(mytext, '/');

id | amount 
-: | :-----------
 1 | column_name1
 1 | column_name2
 1 | column_name3

db<>fiddle here