ID	A	B	C
L1	1	0	1
L2	1	1	1
L3	0	0	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

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

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

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