How do I return a list of all combinations of column names in SQL, when the output for each of those column names equates to 1 (TRUE)?

For example, here's what a sample dataset looks like in which ID, A, B, C are my column names:

ID	A	B	C
L1	1	0	1
L2	1	1	1
L3	0	0	1

Here's what I would like the output to show, when the value of that column name equates to 1:

ID	Combination
L1	A
L1	C
L1	A,C
L2	A
L2	B
L2	C
L2	A,B
L2	B,C
L2	A,C
L2	A,B,C

and so on...

Please note this is just a sample representation of what my dataset looks like, and what I am hoping the output should look like. In my actual dataset, I have over a 100 columns and a few million rows.

You can use CROSS APPLYs to effectively join your starting data with a pair of values - one null value (always present) and and one non-null value (conditionally present). Repeat for each source column and then concatenate those intermediate results.

For that last step, the CONCAT_WS() function will nicely handle the combination of null and non-null values while inserting delimiters (commas) in the appropriate places. Add a check to exclude the empty case and you should have the desired results.

SELECT DS.ID, Result.Combination
FROM Dataset DS
CROSS APPLY (
    SELECT CAST(NULL AS VARCHAR(2)) AS Value
    UNION ALL
    SELECT 'A' WHERE DS.A = 1
) A
CROSS APPLY (
    SELECT CAST(NULL AS VARCHAR(2)) AS Value
    UNION ALL
    SELECT 'B' WHERE DS.B = 1
) B
CROSS APPLY (
    SELECT CAST(NULL AS VARCHAR(2)) AS Value
    UNION ALL
    SELECT 'C' WHERE DS.C = 1
) C
CROSS APPLY (
    SELECT CONCAT_WS(',', A.Value, B.Value, C.Value) AS Combination
) Result
WHERE Result.Combination > ''
ORDER BY
    DS.ID,
    LEN(Result.Combination),
    Result.Combination

Here is a more compact variation:

SELECT DS.ID, Result.Combination
FROM Dataset DS
CROSS APPLY (SELECT 'A' AS Val WHERE DS.A = 1 UNION ALL SELECT NULL) A
CROSS APPLY (SELECT 'B' AS Val WHERE DS.B = 1 UNION ALL SELECT NULL) B
CROSS APPLY (SELECT 'C' AS Val WHERE DS.C = 1 UNION ALL SELECT NULL) C
CROSS APPLY (SELECT CONCAT_WS(',', A.Val, B.Val, C.Val) AS Combination) Result
WHERE Result.Combination > ''
ORDER BY DS.ID, LEN(Result.Combination), Result.Combination

See this db<>fiddle for a demo.

Results:

If you have many columns to deal with, you might consider looking into dynamic SQL techniques to generate the needed query, which might yield something like this.

The following code, available in a dbfiddle, demonstrates one approach. You can use the alternate select statements at the end of the common table expression (CTE) to examine the intermediate results and better understand the steps involved.

-- Sample data.
declare @Samples as Table ( Id VarChar(2), A Int, B Int, C Int );
insert into @Samples ( Id, A, B, C ) values
( 'L1', 1, 0, 1 ),
( 'L2', 1, 1, 1 ),
( 'L3', 0, 0, 1 );
select * from @Samples;
-- Query the data.
with
ConvertedColumns as (
-- Convert the non-Id columns from numbers to strings.
select S.Id, Cols.AC, Cols.BC, Cols.CC
from @Samples as S
cross apply ( select
case when S.A = 1 then 'A' end as AC,
case when S.B = 1 then 'B' end as BC,
case when S.C = 1 then 'C' end as CC ) as Cols ),
Combos as (
-- Generate all of the interesting combinations of values.
select CCols.Id, CCols.AC, CCols.BC, CCols.CC
from ConvertedColumns as CCols
group by cube ( Id, AC, BC, CC )
having Id is not NULL ),
ExtendedCombos as (
-- Extend the combinations with the comma-separated list of values and column counts.
select Id, X1.CSL, X2.ZBColumns
from Combos
cross apply (
-- Generate the comma-separated list of column values.
select Concat_WS( ',', AC, BC, CC ) as CSL ) as X1
cross apply (
-- Calculate the zero-based number of columns in the CSL.
--   Old trick that assumes none of the column values contain commas.
--   This lets us properly sort the results even if some column values have
--     varying lengths.
select Len( X1.CSL ) - Len( Replace( X1.CSL, ',', '' ) ) as ZBColumns ) as X2
where X1.CSL != '' )
-- You can use the alternate   select   stamenets to see the intermediate results.
--  select * from ConvertedColumns;
--  select * from Combos;
--  select * from ExtendedCombos;
select distinct Id, CSL, ZBColumns
from ExtendedCombos
order by
-- Order by the   Id   ...
Id,
-- ... and the number of columns in the combination ...
ZBColumns,
-- ... and finally the combinations themselves.
CSL;