SQL Server - PIVOT

SET @dinamicPivot =

	N'SELECT id, nombre, ' + @columnName + ', importe

	FROM

		(

			SELECT  CGA.id, CGA.nombre, CGA.nombre2 As columna, CGA.baseImponible As importe

			FROM	 tablaPrincipal AGF

					INNER JOIN tabla2 TC ON TC.id = AGF.id

		) As miPivot

	PIVOT

		(

		AVG(miPivot.BI)

		FOR miPivot.columna IN (' + @columnName + ')

		) As pivotable

	ORDER BY fecha'

create table TablaPrincipal  (ID INT, NOMBRE VARCHAR(50))

INSERT INTO TablaPrincipal VALUES(

--id nombre

1, 'nombre1'),

(2, 'nombre2'),

(3, 'nombre3')

create table TablaSecundaria (id int, idTablaPrincipal int, [gastos] varchar(20), [importe] money)

--id idTablaPrincipal gastos importe

INSERT INTO TablaSecundaria VALUES(

1, 1, 'ITV', 100),

(2, 2, 'gasolina', 50),

(3, 2, 'Peaje', 200),

(4, 3, 'ITV', 100)

declare @columnas varchar(max)

set @columnas = ''

select @columnas = coalesce(@columnas + '[' + cast(gastos as varchar(12)) + '],', '')

FROM (select distinct gastos from TablaSecundaria) as dtm

set @columnas = left(@columnas,LEN(@columnas)-1)

DECLARE @SQLString nvarchar(500);

set @SQLString = N'

    SELECT *

	FROM

    (SELECT TablaPrincipal.[id], [nombre], [gastos], [importe] 

        FROM TablaPrincipal inner join TablaSecundaria on TablaPrincipal.id = TablaSecundaria.id ) AS SourceTable

    PIVOT

    (

    AVG([importe])

    FOR [gastos] IN (' + @columnas + ')

    ) AS PivotTable;'

-- print @SQLString

EXECUTE sp_executesql @SQLString

set @SQLString = N'

    SELECT id, nombre, isnull(gasolina,0) Gasolina, isnull(ITV, 0) ITV, isnull(Peaje,0) Peaje

	FROM

    (SELECT TablaPrincipal.[id], [nombre], [gastos], [importe] 

        FROM TablaPrincipal inner join TablaSecundaria on TablaPrincipal.id = TablaSecundaria.idTablaPrincipal ) AS SourceTable

    PIVOT

    (

    AVG([importe])

    FOR [gastos] IN (' + @columnas + ')

    ) AS PivotTable;'

-- print @SQLString

EXECUTE sp_executesql @SQLString

set @SQLString = N'

    SELECT *     -- Quitas los nombres de las columnas, listo

	FROM

    (SELECT TablaPrincipal.[id], [nombre], [gastos], [importe] 

        FROM TablaPrincipal inner join TablaSecundaria on TablaPrincipal.id = TablaSecundaria.idTablaPrincipal ) AS SourceTable

    PIVOT

    (

    AVG([importe])

    FOR [gastos] IN (' + @columnas + ')

    ) AS PivotTable;'