declare  @stroka table( id int ,name varchar(512) ) 
insert into @stroka ( id, name  )	values( 1, 'Сказки' )
insert into @stroka ( id, name)    values( 1, 'Повести' )
insert into @stroka ( id, name)	values( 1, 'Онегин' )
insert into @stroka ( id, name)   values( 1, 'Капитанская дочка' )
insert into @stroka ( id, name)	values( 1, 'Онегин1' )
insert into @stroka ( id, name)	values( 1, 'Капитанская дочка2' )
insert into @stroka ( id, name)	values( 2, 'Реки Времён' )
insert into @stroka ( id, name)	values( 2, 'Я свободен' )
insert into @stroka ( id, name)	values( 2, 'Реки Времён2' )
insert into @stroka ( id, name)	values( 2, 'Я свободен2' )
insert into @stroka ( id, name)	values( 2, 'Я свободен3' )
insert into @stroka ( id, name)	values( 3, 'Пророк' )
insert into @stroka ( id, name)	values( 3, 'Мцыри' )
insert into @stroka ( id, name)	values( 3, 'Маскарад' )
;
with  spisok_npp as (
select id,name,
row_number() over ( partition by  id order by  name) as npp
 from @stroka 
  ),
 spisok as ( 
   select id
   ,name  
   ,npp  
   from spisok_npp  where npp =1	 
 union all 
 select  s.id, convert( varchar(512), s1.name + ', ' + s.name  ),  s.npp
    from  spisok_npp s 
	inner join spisok  s1	
       on s.npp = s1.npp + 1 
	   	  and s.id = s1.id
  ) ,
maxim as (
select id, max(npp) as npp
 from spisok    
  group by id 
 )   --- результат:
 select m.id , s.name from  maxim m
  inner join spisok s
  on s.id = m.id
    and s.npp = m.npp
  order by m.id