You can use CROSS APPLY s 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:
| ID | Combination |
| ------------ | ------------ |
| L1 | A |
| L1 | C |
| L1 | A,C |
| L2 | A |
| L2 | B |
| L2 | C |
| L2 | A,B |
| L2 | A,C |
| L2 | B,C |
| L2 | A,B,C |
| L3 | C |

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;