column alias in dynamic pivot I need help with a dynamic pivot query. I would like to modify the column name (alias) but I can't get it done in the pivot. Please help. The query itself works perfectly but the username contains the domain\username format. I am trying to drop the "domain\" portion. I cannot control what is in or inserted in the database nor can I make any structure changes. I have tried "replace(x,'domain\','') but again, I can't get it to work in the pivot.

Here is what I have
data table:

requestID dateClosed              closedby
--------- ----------------------- -----------
1         2016-06-12 10:34:09.000 DOMAIN\Joe
2         2016-06-12 23:35:09.000 DOMAIN\Bill
3         2016-06-12 08:02:09.000 DOMAIN\Matt
4         2016-06-13 21:46:09.000 DOMAIN\Joe
5         2016-06-13 17:34:09.000 DOMAIN\Bill
6         2016-06-14 16:39:09.000 DOMAIN\Matt
7         2016-06-14 12:38:09.000 DOMAIN\Joe
8         2016-06-14 12:37:09.000 DOMAIN\Bill
9         2016-06-15 11:36:09.000 DOMAIN\Matt
10        2016-06-16 10:35:09.000 DOMAIN\Joe
11        2016-06-13 10:35:09.000 DOMAIN\Bill
12        2016-06-15 21:46:09.000 DOMAIN\Joe
13        2016-06-15 21:47:09.000 DOMAIN\Joe
14        2016-06-15 21:48:09.000 DOMAIN\Joe


Here is my ouput after PIVOT

date                    DOMAIN\Bill DOMAIN\Joe  DOMAIN\Matt
----------------------- ----------- ----------- -----------
2016-06-12 00:00:00.000 1           1           1
2016-06-13 00:00:00.000 2           1           0
2016-06-14 00:00:00.000 1           1           1
2016-06-15 00:00:00.000 0           3           1
2016-06-16 00:00:00.000 0           1           0


I need to remove the "Domain\" portion from the column alias. Replace() didn't work for me. SQl v2012

Here is my query

--dynamic pivot
declare @cols as nvarchar(max),
    @query as nvarchar(max)

set @colS = STUFF((SELECT DISTINCT ',' + QUOTENAME(r.closedby)
    from MYRequests r
    where r.dateclosed &gt; getdate()-30
    for XML path(''), type
    ).value('.','NVARCHAR(MAX)'),1,1,'')

SET @QUERY = 'select * from (
    select 
    DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) as [date],
    closedby
    from MyRequests
    where DateAdd(Day, DateDiff(Day, 0, dateclosed), 0) &gt; getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in  
    ('+ @cols + '))
    as PVT '

execute(@query)


Here is sample filler data:

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')

    select * from myrequests


Please help.
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);