Try this:
DECLARE @Columns VARCHAR(MAX) = '',
@Columns2 VARCHAR(MAX) = '',
@Sql NVARCHAR(MAX) = '';
SELECT @Columns = STUFF((SELECT DISTINCT ',', + QUOTENAME([ActivityDescription])
FROM #TempData FOR XML PATH ('')), 1, 1, '');
SELECT @Columns2 = STUFF((SELECT DISTINCT ',', + 'MAX('+QUOTENAME([ActivityDescription]) + ') AS ' + QUOTENAME([ActivityDescription])
FROM #TempData FOR XML PATH ('')), 1, 1, '');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Container Movement and Increase Charges])', 'CAST(MAX([Container Movement and Increase Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Container Tracking Charges])', 'CAST(MAX([Container Tracking Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Contrainer Ground Rent Charges])', 'CAST(MAX([Contrainer Ground Rent Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Documentation Charges])', 'CAST(MAX([Documentation Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Fuel Charges])', 'CAST(MAX([Fuel Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Handling and PNR Movement Charges])', 'CAST(MAX([Handling and PNR Movement Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Insurance Charges])', 'CAST(MAX([Insurance Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Seal Charges])', 'CAST(MAX([Seal Charges]) AS VARCHAR(20))');
SET @Columns2 = REPLACE(@Columns2, 'MAX([Weighment Charges])', 'CAST(MAX([Weighment Charges]) AS VARCHAR(20))');
--PRINT(@Columns2)
--DROP TABLE IF EXISTS ##Temp_Output;
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Output;
END
SET @sql = '
SELECT
[PayerName],
[ContainerNo],
CAST([Size] AS VARCHAR(20)) AS [Size],
[Type],
CAST([CHAName] AS VARCHAR(20)) AS [CHAName],
[ActGateinDate],
[ContainerAgentName],
[ImporterName],' +
@Columns2 + '
INTO ##Temp_Output
FROM (
SELECT *
FROM #TempData) AS Src
PIVOT(MAX([Amount]) FOR [ActivityDescription] IN (' + @Columns + ')
) pv
GROUP BY [PayerName], [ContainerNo], [Size], [Type], [CHAName], [ActGateinDate], [ContainerAgentName], [ImporterName]
';
PRINT(@sql)
EXEC(@sql)
--DROP TABLE IF EXISTS ##Temp_Columns;
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Columns;
END
SELECT [column_id], [name]
INTO ##Temp_Columns
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..##Temp_Output')
SELECT @Columns = STUFF(
(
SELECT ',',+ QUOTENAME([name])
FROM ##Temp_Columns
ORDER BY [column_id]
FOR XML PATH ('')
),
1,
1,
''
);
--PRINT(@Columns)
--DROP TABLE IF EXISTS ##Temp_Columns_Pivot;
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Columns_Pivot;
END
SET @sql = N'
SELECT *
INTO ##Temp_Columns_Pivot
FROM (
SELECT [name]
FROM ##Temp_Columns
) AS src
PIVOT (
MAX([name]) FOR [name] IN (' + @Columns + ')
) AS pvt
'
EXEC(@sql);
-- Final output
SELECT * FROM ##Temp_Columns_Pivot
UNION ALL
SELECT * FROM ##Temp_Output;
--Drop temporary tables
IF OBJECT_ID(N'tempdb..##Temp_Columns') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Columns;
END
IF OBJECT_ID(N'tempdb..##Temp_Columns_Pivot') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Columns_Pivot;
END
IF OBJECT_ID(N'tempdb..##Temp_Output') IS NOT NULL
BEGIN
DROP TABLE ##Temp_Output;
END
IF OBJECT_ID(N'tempdb..#TempData') IS NOT NULL
BEGIN
DROP TABLE #TempData;
END
GO