SQL cross tab pivot query

Question

I have a table as below.

CaseID   StatusID    StageID     CaseRegisterTime   City
1        1            5            datetime         XYZ
2        1            5            datetime         ABC

Now I want its Citywise count and only for only specific dates, and also in condition for statusid = 1 and stageid = 5.

Cities     CurrentDate-1    CurrentDate-2   January2012-CurrentDate-3
XYZ           5                  51                  5008
JUS           0                   0                   125
ABC           1                   0                   48

I want my header to group cases for CaseRegisterTime as shown above.

Please help.

try googling pivot table queries sql you'll find lots of examples of what you are trying to do. — Brian, Mar 22 '12 at 13:47
I tried pivoting but main problem is date column which I have only limited, as shown in above sample output for limited dates not want to look for all other dates. — k-s, Mar 22 '12 at 13:51
Where do you get the dates from? And generally, when we ask "what have you tried?" we want to see code — Adriano Carneiro, Mar 22 '12 at 13:54
@Keyur, please put up more meaningful data (even if it's fake)...it is very difficult to see where your desired results are coming from. Are those CurrentDate columns supposed to be counts, sums or what? — Tim Lehner, Mar 22 '12 at 14:08
@Brian Just Google it is not helpful.. since when others ARE doing a Google search they continually get back results that say "just Google it". Causes a nasty recursion. — Volvox, Mar 15 '13 at 21:38

JotaBe · Accepted Answer · 2012-03-22T14:17:26.350

Use case when to convert your dates of interest to 'CurrentDate-1' and 'CurrentDate-2', and then pivot the results, using this strings as the new columns.

Alternatively, you can do something like this:

select City, sum(Date1) as Date1, sum(Date2) as Date2
from(
select City, 
    case when CaseRegisterTime='2012-01-01' then 1 else 0 end as Date1,
    case when  CaseRegisterTime='2012-15-01' then 1 else 0 end as Date2
from sample
) as T
group by City

you'd also have to filter out the registers which doesn't have the desired date.

Tim Lehner · Answer 2 · 2012-03-22T14:52:47.667

Here's one of many ways to do it in SQL Server 2008 (using the Date datatype):

select distinct a.City as Cities
    , (select count(*)
        from MyTable
        where CaseRegisterTime >= cast(getdate() - 1 as date)
            and CaseRegisterTime < cast(getdate() - 0 as date)
            and StatusID = a.StatusID
            and StageID = a.StageID
            and City = a.City
    ) as [CurrentDate-1]
    , (select count(*)
        from MyTable
        where CaseRegisterTime >= cast(getdate() - 2 as date)
            and CaseRegisterTime < cast(getdate() - 1 as date)
            and StatusID = a.StatusID
            and StageID = a.StageID
            and City = a.City
    ) as [CurrentDate-2]
    , (select count(*)
        from MyTable
        where CaseRegisterTime >= cast('20120101' as date)
            and CaseRegisterTime < cast(getdate() - 2 as date)
            and StatusID = a.StatusID
            and StageID = a.StageID
            and City = a.City
    ) as [January2012-CurrentDate-3]
from MyTable a
where a.StatusID = 1
    and a.StageID = 5

Update

The case and sum method @JotaBe uses is about twice as fast on my box (with many less scans and reads), so here's what that could look like:

select a.City as Cities
    , sum(a.[CurrentDate-1]) as [CurrentDate-1]
    , sum(a.[CurrentDate-2]) as [CurrentDate-2]
    , sum(a.[January2012-CurrentDate-3]) as [January2012-CurrentDate-3]
from (
    select City
        , case when CaseRegisterTime >= cast(getdate() - 1 as date)
                and CaseRegisterTime < cast(getdate() - 0 as date)
                then 1 else 0 end [CurrentDate-1]
        , case when CaseRegisterTime >= cast(getdate() - 2 as date)
                and CaseRegisterTime < cast(getdate() - 1 as date)
                then 1 else 0 end [CurrentDate-2]
        , case when CaseRegisterTime >= cast('20120101' as date)
                and CaseRegisterTime < cast(getdate() - 2 as date)
                then 1 else 0 end [January2012-CurrentDate-3]
    from MyTable 
    where StatusID = 1
        and StageID = 5
) as a
group by a.City

score 0 · Answer 3 · answered Mar 22 '12 at 15:07

Something like this will do:

begin tran;
go
create table #t1(
    ID int identity,
    City varchar,
    RegisterDate  datetime
);
declare  @firstDate datetime, @secondDate datetime;
 set @firstDate = '2012-1-1';
 set @secondDate = '2012-1-2';
insert into #t1 values
    ('A', @firstDate),
    ('A', @firstDate),
    ('B', @firstDate),
    ('B', @firstDate),
    ('B', @firstDate),
    ('A', @secondDate),
    ('A', @secondDate),
    ('A', @secondDate),
    ('B', @secondDate),
    ('B', @secondDate);

select * from #t1;

select pvt.*
from(
    select ID, City, RegisterDate
    from #t1
) a
pivot(
    count(a.ID)
    for a.RegisterDate in ([2012-1-1], [2012-1-2])
)   as pvt;

drop table #t1;
go
rollback tran;

SQL cross tab pivot query

3 Answers3