SQL Server query that add a column in a Select Query, Sql Server

Language: Layout:

create table     CX_SOLICITUD_SERVICIO (NU_CONS_SOSA_SOSE int, CD_CODI_SER_SOSE varchar(32))
insert into CX_SOLICITUD_SERVICIO values
 (777,'i684000')
,(777,'i705210')
,(777,'i595101')
,(778,'i684000')
,(778,'i549201')
,(756,'i132300')
,(763,'i132300')
,(758,'i132300')
,(761,'i132300')
,(761,'i132301')

declare @cols nvarchar(max);
    declare @sql  nvarchar(max);
    
      select @cols = stuff((
        select distinct 
          ',' + quotename('CD_CODI_SER_SOSE_'
              +convert(nvarchar(10),row_number() over (
                  partition by NU_CONS_SOSA_SOSE 
                  order by     CD_CODI_SER_SOSE 
              ))
              )
          from CX_SOLICITUD_SERVICIO 
          for xml path (''), type).value('.','nvarchar(max)')
        ,1,1,'');
        
    select @sql = '
     select NU_CONS_SOSA_SOSE, ' + @cols + '
      from  (
        select 
            NU_CONS_SOSA_SOSE
          , CD_CODI_SER_SOSE
          , rn=''CD_CODI_SER_SOSE_''+convert(nvarchar(10),row_number() over (
              partition by NU_CONS_SOSA_SOSE 
              order by     CD_CODI_SER_SOSE 
              ))
          from CX_SOLICITUD_SERVICIO
          ) as a
     pivot (max([CD_CODI_SER_SOSE]) for [rn] in (' + @cols + ') ) p';
     select @sql as CodeGenerated;
     exec sp_executesql @sql;

View schema
Execution time: 0,02 sec, rows selected: 9, rows affected: 10, absolute service time: 0,16 sec