UNPIVOT in SQL Server

Question

I have a table as below:

I need to convert this table's rows into columns

Expected output:

depotCode | containerYardCode | chargeCode | 20"GP  | 40"GP |YAC 20"GP|YAC 40"GP
-------------------------------------------------------------------------------
HQ        |CT1 KCT            | DGC        | 25.000 |25.000 | NULL    |NULL    
HQ        |HARBOUR YARD       | DGC        |21.000  |32.000 |18.000   |28.000

Answer 1

Sample data:

declare @t table (depotCode varchar(2), containerYardCode varchar(25), chargeCode varchar(3), cargoTypeCode varchar(10), effectiveDate datetime, rate numeric(5,3), yardActualCost numeric(5,3), includeGST bit)
insert into @t values ('HQ', 'CT1 KCT', 'DGC', '20"GP', '6/1/2015', 25, null, 0)
insert into @t values ('HQ', 'CT1 KCT', 'DGC', '40"GP', '6/1/2015', 25, null, 0)
insert into @t values ('HQ', 'HARBOUR YARD', 'DGC', '20"GP', '1/1/2017', 21, 18, 0)
insert into @t values ('HQ', 'HARBOUR YARD', 'DGC', '40"GP', '1/1/2017', 32, 28, 0)

I think you need to join 2 pivot queries together, like this:

SELECT sq1.depotCode, sq1.containerYardCode, sq1.chargeCode, [20"GP], [40"GP], [YAC 20"GP], [YAC 40"GP]
FROM
(
    SELECT depotCode, containerYardCode, chargeCode, [20"GP], [40"GP]
    FROM (SELECT depotCode, containerYardCode, chargeCode, cargoTypeCode, rate FROM @t)t
    PIVOT (sum(rate) for cargoTypeCode in ([20"GP], [40"GP]))p
)sq1
INNER JOIN 
(
    SELECT depotCode, containerYardCode, chargeCode, [20"GP] as [YAC 20"GP], [40"GP] as [YAC 40"GP]
    FROM (SELECT depotCode, containerYardCode, chargeCode, cargoTypeCode, yardActualCost FROM @t)t
    PIVOT (sum(yardActualCost) for cargoTypeCode in ([20"GP], [40"GP]))p
)sq2 ON sq1.depotCode = sq2.depotCode AND sq1.containerYardCode = sq2.containerYardCode AND sq1.chargeCode = sq2.chargeCode

And here's the output:

depotCode   containerYardCode   chargeCode  20"GP   40"GP   YAC 20"GP   YAC 40"GP
HQ          CT1 KCT             DGC         25.000  25.000  NULL        NULL
HQ          HARBOUR YARD        DGC         21.000  32.000  18.000      28.000

The key is in the FROM clauses of the subqueries - only include the columns necessary for each individual pivot, and then join the subqueries on the common columns (e.g. depotCode, containerYardCode, chargeCode).