Select not null columns as a comma separated string from dynamic sql
how to get column values in comma separated sql server
sql split delimited string into columns
using comma separated value parameter strings in sql in clauses
how to get comma separated values in sql query
sql split string by delimiter
split comma separated string into multiple columns sql server
sql split string by space
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:
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.
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
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');
SQL Tip: Create comma separated string from values in a column , SQL Tip: Create comma separated string from values in a column (& COALESCE ) This function will return the first non-NULL value in the list of items given. SELECT @List = COALESCE (@List + N '[What you want to separate the values with]' SQL Tip: Returning a portion of a string field - dynamically� SQL Tip: Create comma separated string from values in a column (& COALESCE) sqlbenjamin SQL Tip October 10, 2017 April 20, 2019 2 Minutes [edit: April 2019] This was originally posted to my internal (to Microsoft) blog on March 19, 2012 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to.
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)
Using comma separated value parameter strings in SQL IN clauses , Passing a comma separated list of values to an SQL query is all very well, but you can't DECLARE @LIST VARCHAR(200) SET @LIST = '1,3' SELECT Id, Descr FROM TABLE (id int not null) AS BEGIN ;-- Ensure input ends with comma SET (No column name) (No column name) (No column name) 4 123 456,789,. In summary, the PARSENAME function is a handy addition to your T-SQL toolkit for writing queries involving delimited data. It allows for parsing out and returning individual segments of a string value into separate columns. Since the PARSENAME function breaks down the string, you are not obligated to return all the delimited values.
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
STRING_SPLIT (Transact-SQL), STRING_SPLIT outputs a single-column table whose rows contain the substrings . As an example, the following SELECT statement uses the space Parse a comma-separated list of values and return all non-empty tokens: Such an anti- pattern can involve the creation of a dynamic SQL string in the� So we could take the above data, and use the STRING_AGG() function to list all the task names in one big comma separated list. Like this: SELECT STRING_AGG(TaskName, ', ') FROM Tasks; Result: Feed cats, Water dog, Feed garden, Paint carpet, Clean roof, Feed cats Of course, it doesn’t necessarily need to be separated by a comma.
MySQL FIND_IN_SET Function with Practical Examples, The second parameter haystack is a list of comma-separated strings that to be searched. The FIND_IN_SET() function returns an integer or a NULL value depending on if the needle is a constant string and the haystack is a column of type SET . SELECT name, belts FROM divisions WHERE NOT FIND_IN_SET(' black',� Here Mudassar Ahmed Khan has explained with an example, how to use the SQL Server COALESCE function to select column values in Table as comma separated (delimited) string in SQL Server. COALESCE function can be used to get comma separated (delimited) values from Table in the following SQL Server versions i.e. 2005, 2008, 2008R2, 2012 and 2014. TAGs: SQL Server
The Curse and Blessings of Dynamic SQL, A dynamic pivot is the antithesis of this. perfect sense to have the comma in the variable. colname sysname NOT NULL UNIQUE) INSERT SELECT @sql += ' FROM Orders O JOIN [Order One is a string literal, and the second is a column alias. How to split a column with delimited string into multiple columns. Long time ago I found that data might come in many different formats, mostly related with the data source, Cellphone Numbers, User Names, addresses are some examples of data that commonly comes in delimited format.
Dynamic Search Conditions in T-SQL, Or you can use dynamic SQL to build a query string which includes only the search In the general case, there is a search form where the user can select between many @employeestr is a comma-separated string with employee IDs, while The reason is that for the customer on this order, the column Region is NULL. We want to join the values in the CustomerID column into a comma delimited list which would result in: 3, 4, 5 One way to solve this would be to use a SQL Cursor and select the values from the column and then loop through the resultset one row at a time and append the value from the row.
- UNION ALL the non-null's to get second option.
exec ('select ' + @columnList + ' from myTable')is a bad idea. SQL injection is not your friend.
- which sql server version?
@columnListis 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.
- Mssql 2016 v13.0.x
- Worth to mention, that
STRING_AGG()will need version 2017+. The OP stated in a comment, there's v2016 in use....
- Oh right... I'm even the one to ask which version they're using exactly with
STRING_AGGin mind, and yet I confused 2016 with 2017. I'll add a note to my answer.