1

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

  • For those of us unfamiliar with Postgresql what does the unnest(array do? Can you provide example data and desired results? – Martin Smith Dec 27 '18 at 14:15
  • string_split starts at SqlServer 2016, you should use some function like stackoverflow.com/a/10914602/3270427. – McNets Dec 27 '18 at 14:19
  • 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. – sql_mind Dec 27 '18 at 14:26
  • Then someone has added a function with this name. – McNets Dec 27 '18 at 14:31
3

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)
|improve this answer|||||
  • 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? – sql_mind Dec 28 '18 at 9:44
  • Seems I misunderstood your requirements. Sure, I'll edit my answer accordingly. – Eitan Blumin Dec 29 '18 at 3:57
  • 1
    I used the CROSS APPLY and got my desired result. Thanks! – sql_mind Jan 2 '19 at 14:10
  • Pleasure @MarG :) – Eitan Blumin Jan 3 '19 at 15:49
0
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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.