SELECT
NewColumn,
[Old] = MAX(CASE WHEN ItemState = 'old' THEN [Stuff] ELSE NULL END),
[New] = MAX(CASE WHEN ItemState = 'new' THEN [Stuff] ELSE NULL END),
[Used] = MAX(CASE WHEN ItemState = 'used' THEN [Stuff] ELSE NULL END),
[stolen] = MAX(CASE WHEN ItemState = 'stolen' THEN [Stuff] ELSE NULL END)
FROM @Actual
CROSS APPLY (VALUES
('Cars', car),
('Trucks', CAST(Truck AS VARCHAR(10))),
('Boats', CAST(Boats AS VARCHAR(10))),
('Planes', CAST(Planes AS VARCHAR(10)))
) x (NewColumn, [stuff])
GROUP BY x.NewColumn
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden