1

This question already has an answer here:

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

marked as duplicate by Philᵀᴹ, Daniel Hutmacher, Paul White sql-server Feb 24 '16 at 3:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from serverfault.com Feb 23 '16 at 20:37

This question came from our site for system and network administrators.

  • Might I suggest a CTE for the data source instead of going directly against the table? Do the replace inside the CTE. – Jonathan Fite Feb 23 '16 at 21:46
2

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 > 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) > getdate()-30
    )
    as s
PIVOT
    (count(closedby) for [closedby] in  
    (' + @cols + '))
    as PVT ';

execute(@query);
  • Many thanks @Jonathan. Works great. I need to brush up on CTE. – matt Forchette Feb 24 '16 at 14:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.