DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)
 
SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([name_disc])
                        FROM disciplines  FOR XML PATH('') ), 1, 1, '')
 
SET @sqlquery = 'SELECT * FROM
      (SELECT (s.family+char(1)+s.name+parentname) as fio, rating, name_disc 
       FROM ratings as Uo inner join students as S on uo.student=s.idstudent inner join disciplines as D on d.iddiscipline=uo.discipline) base
       PIVOT (SUM(rating) FOR [name_disc] 
       IN (' + @cols + ')) AS finalpivot' 
 
EXECUTE ( @sqlquery )

if OBJECT_ID('tempdb..#data') is not null drop table #data
create table #data(id int,name varchar(10),val int)
insert into #data values(1,'name1',10),(1,'name2',20),(1,'name3',30),(2,'name1',100),(2,'name2',200),(2,'name3',300)
select * from #data

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([name])
                        FROM #data  FOR XML PATH('') ), 1, 1, '')

if OBJECT_ID('tempdb..#result') is not null drop table #result
create table #result(id int)
set  @sqlquery = 'alter table #result add ' +REPLACE(@cols,',',' int,')+ ' int'                     
exec(@sqlquery)

                        
SET @sqlquery = 'SELECT id,'+@cols+' FROM #data PIVOT (SUM([val]) FOR [name] 
       IN (' + @cols + ')) AS finalpivot' 
 
INSERT INTO #result EXECUTE ( @sqlquery )

select * from #result