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 |