SQL Server 2017

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

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');


colName


txt2

txt1

txt3

txt4


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');


colName


txt1

txt2

txt3

txt4