Dynamic bit-based flattening of multiple rows by pivoting into additional columns

I have data that looks like this:

ID  | Value
-----------
1   |   a 
1   |   b 
2   |   a
2   |   c
3   |   a
3   |   d

And I would like it to look like this:

ID  | Value_a  |  Value_b | Value_c | Value_d
---------------------------------------------
1   |    1     |     1    |    0    |    0
2   |    1     |     0    |    1    |    0
3   |    1     |     0    |    0    |    1

I think a dynamic conditional aggregation is required. Any help would be appreciated.

Answer

Here is a sample implementation of dynamic conditional aggregation:

--create test table
create table #values (
     [ID] int
    ,[Value] char(1))

--populate test table
insert into #values
values
     (1, 'a') 
    ,(1, 'b') 
    ,(2, 'a')
    ,(2, 'c')
    ,(3, 'a')
    ,(3, 'd')

--declare variable that will hold dynamic query
declare @query nvarchar(max) = ' select [ID] '

--build dynamic query and assign it to variable 
select 
    @query = @query + max(',max(case when [value] = ''' 
    + [value] + ''' then 1 else 0 end) as Value_' + [value] ) 
from 
    #values 
group by 
    [value]

--add group by clause to dunamic query
set  @query = @query + ' from #values group by [id]'

--execute dynamic query
exec (@query) 

this is the result:

Now you can add a value (for example id = 4 and value = ‘e’) replacing the original insert with this one:

insert into #values
values
     (1, 'a') 
    ,(1, 'b') 
    ,(2, 'a')
    ,(2, 'c')
    ,(3, 'a')
    ,(3, 'd')
    ,(4, 'a')
    ,(4, 'e')

this is the new output: