Select not null columns as a comma separated string from dynamic sql

Question

I am trying to do my best to avoid using cursors. There is a comma separated list of columns in nvarchar variable that looks like this:

@columnList = 'col1,col2,col5'

There is a table with lots of varchar columns:

myTable: [col1],[col2],[col3],[col4],[col5],[col6],[col7]

This is how I select data using a dynamic sql:

exec ('select ' + @columnList + ' from myTable')

This query returns the following results:

[col1], [col2] , [col5]
 null , "txt1" ,  null
"txt2",  null  ,  null
 null , "txt3" , "txt4"

This is what I need to get:

@resultList = "txt1,txt2,txt3,txt4"

How do I select a comma separated string containing not-null values only? I know how to convert a table to comma separated string, so getting something like:

 [column]
  "txt1"
  "txt2"
  "txt3"
  "txt4"

Is also fine. Any suggestions? Feel free to suggest a basic approach, I don't expect you to write the actual code for me.

`exec ('select ' + @columnList + ' from myTable')` is a bad idea. SQL injection is not your friend. — Thom A, Apr 05 '19 at 09:20
`@columnList` is being generated on a server with another query. There is no way one can inject something in there. Feel free to suggest a better approach tho. — Hirasawa Yui, Apr 05 '19 at 09:23
In a nutshell, I'd recommend: 1. unpivot with cross apply 2. concatenate into comma-seperated using xml path, where is not null — George Menoutis, Apr 05 '19 at 09:29

Sebastian Brosch · Accepted Answer · 2019-04-05T10:09:10.903

You can use a solution like the following, using just a REPLACE to create the SQL query:

DECLARE @columnList VARCHAR(100)
SET @columnList = 'col1,col2,col5'
SET @columnList = REPLACE(@columnList, ',', ' AS colName FROM myTable UNION ALL SELECT ');

EXEC('SELECT * FROM (SELECT ' + @columnList + ' AS colName FROM myTable)t WHERE NOT t.colName IS NULL');

You can also use a solution using UNPIVOT:

DECLARE @columnList VARCHAR(100);
SET @columnList = 'col1,col2,col5';

EXEC('SELECT colName FROM (SELECT ' + @columnList + ' FROM myTable) t1 UNPIVOT (colName FOR columnNames IN (' + @columnList + ')) AS t2');

demo on dbfiddle.uk

score 0 · Answer 2 · answered Apr 05 '19 at 09:34

Since you mention you already know how to aggregate into comma-seperated, here's how to unpivot your table with cross apply:

select unpivoted.*
from myTable
cross apply
( values
     ('col1',col2)
    ,('col2',col2)
    ,('col3',col3) -- etc
)unpivoted(colname,colval)

MarcinJ · Answer 3 · 2019-04-05T11:14:21.667

0

This would work for SQL Server 2017, on earlier version STRING_AGG usage should be replaced with XML-based solution.

You could first concatenate your results into a single line and then use STRING_AGG to aggregate them into a single string:

;with t as
(
SELECT * FROM (VALUES
   (null  , 'txt1' ,  null),
   ('txt2',  null  ,  null),
   (null  , 'txt3' , 'txt4')) x(col1, col2, col5) 
)
SELECT STRING_AGG(STUFF(CONCAT(',' + col1, ',' + col2, ',' + col5), 1, 1, ''), ',')
  FROM t

CTE is just for showcasing, you can simply do it in your dynamic query:

DECLARE @columnList NVARCHAR(MAX) = 'col1,col2,col5'
DECLARE @query NVARCHAR(MAX) = ''
SELECT @query = 'SELECT STRING_AGG(STUFF(CONCAT('','' + ' + REPLACE(@columnList, ',', ', '','' + ') + '), 1, 1, ''''), '','') from mytable'

EXEC sp_executesql @query

Working example on dbfiddle

edited Apr 05 '19 at 11:14

answered Apr 05 '19 at 09:43

MarcinJ

3,471
2
14
18

1

Worth to mention, that [`STRING_AGG()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017) will need version 2017+. The OP stated in a comment, there's v2016 in use.... – Shnugo Apr 05 '19 at 10:57
Oh right... I'm even the one to ask which version they're using exactly with `STRING_AGG` in mind, and yet I confused 2016 with 2017. I'll add a note to my answer. – MarcinJ Apr 05 '19 at 11:13

Select not null columns as a comma separated string from dynamic sql

3 Answers3