Difficulty in dynamizing columns in MSSQL

Question

Gentlemen, good morning !

I’m having a little trouble building this query in Microsoft SQL 2019

Could someone maybe give me a light ?

I have a base like this

Codfuncionario | cod_centro_custo | perc_rateio

 1         |       1          |    50%
 1         |       2          |    50%
 2         |       1          |    100%

And I need to dynamically transform it:

codFunctioning | cod_centro_custo_1 | perc_valor1 | cod_centro_custo2 | perc_valor2 ..

 1         |        1           |    50%      |       2           |    50%
 2         |        1           |    100%     |     NULL          |    NULL

Answer 1

I was going to the pivot path, but it’s not necessarily what I need, because I want the cost center fields that are in the columns to be in rows.

I was using this query, but it generates only the header with the cost center text in this way

declare @colunas_pivot as nvarchar(max) ,@command_sql as nvarchar(max)

SET @colunas_pivot = Stuff( SELECT DISTINCT ',' + quotename(CODCENCUS) -, ',' + quotename('value') FROM TFPRTF FOR XML PATH (') ), 1, 1, '') print @colunas_pivot set @commandndo_sql = ' select * from ( SELECT CODFUNC , ,SUM(APPORTIONMENT) APPORTIONMENT FROM TFPRTF GROUP BY
CODFUNC , ) inline pivot ( sum(PERCRATEIO) for CODCENCUS in (' + @colunas_pivot + ')) in_columns order by 1' print @comando_sql execute(@command sql) go

And I need you to stay that way :