By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE myTable (
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 VARCHAR(10),
col4 VARCHAR(10),
col5 VARCHAR(10),
col6 VARCHAR(10),
col7 VARCHAR(10)
);
INSERT INTO myTable VALUES
(NULL, 'txt1', NULL, NULL, NULL, NULL, NULL),
('txt2', NULL, NULL, NULL, NULL, NULL, NULL),
(NULL, 'txt3', NULL, NULL, NULL, NULL, NULL),
(NULL, NULL, NULL, NULL, 'txt4', NULL, NULL);
4 rows affected
SELECT STRING_AGG(STUFF(CONCAT(',' + col1, ',' + col2, ',' + col5), 1, 1, ''), ',') FROM myTable
| (No column name) |
|---|
| txt1,txt2,txt3,txt4 |
DECLARE @query NVARCHAR(MAX) = ''
DECLARE @columnList NVARCHAR(MAX) = 'col1,col2,col5'
SELECT @query = 'SELECT STRING_AGG(STUFF(CONCAT('','' + ' + REPLACE(@columnList, ',', ', '','' + ') + '), 1, 1, ''''), '','') from mytable'
EXEC sp_executesql @query
| (No column name) |
|---|
| txt1,txt2,txt3,txt4 |