Good question. Thanks Steve..

Good conceptual question. Thanks Steve.

Good question and a very important concept

Not sure why that would give an error.
But there are a few things other than that which will give your errors.

I also checked your other post out...

Any specific reason as to why you want to do it in 2 steps when you can do it in a single step as shown by Drew?

This should help
SELECT [Dept#],
        MAX( CASE WHEN TransCode = 'wetwear' THEN 'wetwear' ELSE NULL END ) ColName1,
        SUM(...

This is where the PIVOT operator becomes painful to use. CROSS-TAB's are easier to use and understand is such cases.

USE [tempdb];
GO

CREATE TABLE PersonAddress

Good question. Thanks.

Good Question.

Nice question. Thanks.

Duplicate post.
https://www.sqlservercentral.com/Forums/1876106/get-missing-dates-differencedays-and-fill-previous-dates-data-in-sql-server

SELECT di.date, ord.orderid, DATEDIFF( DAY, ord.orderdate, di.date ) missingdays, ord.cost
FROM    [dbo].[dateinfo] di
CROSS APPLY (
              SELECT TOP...