none
CrossTab Query In SQL 2008

    Question

  • Hi Experts,
    I have a calling details table which contains AgentId, CallDate, RespType etc.
    Now I want a crosstab query to get AgentId as row and CallDate as column header. Also want to show number of calls each day for last 7 days.

    Let me clear further: lets say if i select CallDate=07-Nov-2010, then result should look like this:

    AgentId 07-Nov 06-Nov 05-Nov 04-Nov 03-Nov 02-Nov 01-Nov
    ==============================================
    1111 24 10 32 15 16 52 65

    2222 20 25 50 57 61 25 51

    Can any body help me with a sql query for the same.

    Tuesday, November 09, 2010 6:50 AM

Answers

  • You need to use dynamic pivot as the header columns are dynamic(depends on the provided date). see this example.

    create table Callingdetails
    (
    	AgentId int, 
    	CallDate datetime, 
    	RespType int,
    )	
    insert into Callingdetails values (1,'11/5/2010',1),(1,'11/5/2010',1),(2,'11/3/2010',1),(1,'11/8/2010',1),(2,'11/2/2010',1)
    
    
    declare @Date datetime = '11/7/2010'
    declare @Dates nvarchar(max) = '[' + CONVERT(varchar(6),@date,107) + '],' + '[' + CONVERT(varchar(6),@date-1,107) + '],' + 
     '[' + CONVERT(varchar(6),@date-2,107) + '],' + '[' + CONVERT(varchar(6),@date-3,107) + '],' + '[' + CONVERT(varchar(6),@date-4,107) + '],' + 
     '[' + CONVERT(varchar(6),@date-5,107) + '],' + '[' + CONVERT(varchar(6),@date-6,107) + ']' 
    declare @sql nvarchar(max) = 'select * from 
    ( select AgentID, CONVERT(varchar(6),DATEADD(dd,0,DATEDIFF(dd,0,CallDate)),107) as Sday,COUNT(*) as cnt from CAllingDetails group by AgentID,DATEDIFF(dd,0,CallDate) )p
    pivot (max(cnt) for Sday in (' + @Dates + '))pvt'
    exec(@sql)
    
    
    
    Tuesday, November 09, 2010 7:05 AM

All replies

  • You can use the PIVOT operator and dynamic SQL to write a dynamic crosstab/PIVOT report.

    Dynamic crosstab link: http://www.sqlusa.com/bestpractices2005/dynamicpivot/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 09, 2010 6:57 AM
    Moderator
  • You need to use dynamic pivot as the header columns are dynamic(depends on the provided date). see this example.

    create table Callingdetails
    (
    	AgentId int, 
    	CallDate datetime, 
    	RespType int,
    )	
    insert into Callingdetails values (1,'11/5/2010',1),(1,'11/5/2010',1),(2,'11/3/2010',1),(1,'11/8/2010',1),(2,'11/2/2010',1)
    
    
    declare @Date datetime = '11/7/2010'
    declare @Dates nvarchar(max) = '[' + CONVERT(varchar(6),@date,107) + '],' + '[' + CONVERT(varchar(6),@date-1,107) + '],' + 
     '[' + CONVERT(varchar(6),@date-2,107) + '],' + '[' + CONVERT(varchar(6),@date-3,107) + '],' + '[' + CONVERT(varchar(6),@date-4,107) + '],' + 
     '[' + CONVERT(varchar(6),@date-5,107) + '],' + '[' + CONVERT(varchar(6),@date-6,107) + ']' 
    declare @sql nvarchar(max) = 'select * from 
    ( select AgentID, CONVERT(varchar(6),DATEADD(dd,0,DATEDIFF(dd,0,CallDate)),107) as Sday,COUNT(*) as cnt from CAllingDetails group by AgentID,DATEDIFF(dd,0,CallDate) )p
    pivot (max(cnt) for Sday in (' + @Dates + '))pvt'
    exec(@sql)
    
    
    
    Tuesday, November 09, 2010 7:05 AM