column alias in dynamic pivot
Here you go, using a CTE to do the replace.  I used a global temp table instead, but the sample works.

CREATE TABLE ##MYRequests
    (
      [requestID] SMALLINT
    , [dateClosed] DATETIME
    , [closedby] VARCHAR(50)
    );

INSERT  INTO ##MYRequests
        ( [requestID], [dateClosed], [closedby] )
VALUES  ( 1, '20160612 10:34:09 AM', 'DOMAIN\Joe' ),
        ( 2, '20160612 11:35:09 PM', 'DOMAIN\Bill' ),
        ( 3, '20160612 08:02:09 AM', 'DOMAIN\Matt' ),
        ( 4, '20160613 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 5, '20160613 05:34:09 PM', 'DOMAIN\Bill' ),
        ( 6, '20160614 04:39:09 PM', 'DOMAIN\Matt' ),
        ( 7, '20160614 12:38:09 PM', 'DOMAIN\Joe' ),
        ( 8, '20160614 12:37:09 PM', 'DOMAIN\Bill' ),
        ( 9, '20160615 11:36:09 AM', 'DOMAIN\Matt' ),
        ( 10, '20160616 10:35:09 AM', 'DOMAIN\Joe' ),
        ( 11, '20160613 10:35:09 AM', 'DOMAIN\Bill' ),
        ( 12, '20160615 09:46:09 PM', 'DOMAIN\Joe' ),
        ( 13, '20160615 09:47:09 PM', 'DOMAIN\Joe' ),
        ( 14, '20160615 09:48:09 PM', 'DOMAIN\Joe' );

--dynamic pivot
DECLARE @cols AS NVARCHAR(MAX)
  , @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT
                            ',' + QUOTENAME(REPLACE(r.closedby, 'DOMAIN\', ''))
                   FROM     ##MYRequests r
                   WHERE    r.dateClosed &gt; GETDATE() - 30
    FOR           XML PATH('')
                    , TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');




SET @query = ';WITH CTE_Requests AS
(SELECT M.requestID, M.dateclosed, REPLACE(M.closedby, ' + QUOTENAME('DOMAIN\',
                                                              '''') + ', '
    + '''' + '''' + ') AS closedby
FROM ##MYRequests M)
SELECT * from (
    select 
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from CTE_Requests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) &gt; getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in  
    (' + @cols + '))
    as PVT ';

execute(@query);