SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

Msg 195 Level 15 State 10 Line 1
'version' is not a recognized built-in function name.

--DROP TABLE PivotTable

CREATE TABLE PivotTable

(BerthId int, ShipType varchar(20), ShipSize varchar(20));

--Populate Sample records

INSERT INTO PivotTable VALUES(1,'Tanker','RoRo');

INSERT INTO PivotTable VALUES(1,'Tanker','small tanker');

INSERT INTO PivotTable VALUES(1,'Main Cargo', 'Small Main Cargo');

INSERT INTO PivotTable VALUES(2, 'Tanker', 'Small Tanker');

SELECT * FROM PivotTable

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

----Get distinct values of the PIVOT Column

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(ShipType)

FROM (SELECT DISTINCT ShipType FROM PivotTable) AS ShipTypes

--Prepare the PIVOT query using the dynamic

SET @DynamicPivotQuery =

N'SELECT ShipSize, ' + @ColumnName + '

FROM PivotTable

PIVOT(SUM(berthid)

FOR ShipType IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery