SQL Server: Get Column Names based on the Record's Value

Scenario:

I have a table with below Schema

Name	Column1	Column2	Column3	Column4
Gopi	1	1	0	1
Krishna	0	1	1	1

and I want the output in the following format

Name	ColumnsSelected
Gopi	Column1, Column2, Column4,
Krishna	Column2, Column3, Column4,

Solution:

I achieved the above using CROSS APPLY

If the column names are static

SELECT Name,ColumnsSelected=(
SELECT cname+','
FROM   TestTable B
CROSS apply (VALUES('Column1',Column1),
                    ('Column2',Column2),
                    ('Column3',Column3),
                    ('Column4',Column4)) ca (cname, data)
WHERE  data = 1 AND A.Name=B.Name
FOR XML PATH('')) 
FROM TestTable A
Group by name

If the column names are dynamic

DECLARE @collist VARCHAR(MAX)='',
        @sql     NVARCHAR(MAX);
  
SELECT @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),'
FROM   INFORMATION_SCHEMA.columns
WHERE  TABLE_NAME = 'TestTable'
  AND COLUMN_NAME NOT IN ('id','Name')
  AND TABLE_SCHEMA = 'dbo';

SELECT @collist = LEFT(@collist, Len(@collist) - 1);

SET @sql ='
SELECT Name,ColumnsSelected=(
SELECT cname+'', ''
FROM   TestTable B
    CROSS APPLY (VALUES' + @collist
    + ') ca (cname, data)
WHERE data=1 AND A.Name=B.Name
FOR XML PATH(''''))
FROM TestTable A
GROUP BY A.Name ';

EXEC Sp_executesql  @sql;

Happy Coding! 😊

Credits: Stack overflow question

Gopikrishna