|
|
Posted by Erland Sommarskog on 05/28/05 00:16
(rong.guo@gmail.com) writes:
> I would like to make a sort of pivot table using the raw data below,
> but my query wouldn't give me results in the ideal output format
> (please see below). Any idea how to deal with it? Thanks a million!
>
> create table a
> (referid varchar(255)
> ,app_id int
> ,description varchar (255)
> ,time datetime
> ,amount money)
>
> insert into a values('A111',1111, 'Checking', '3/25/2005',6000)
> insert into a values('A111',1112, 'Savings', '4/15/2005',3000)
> insert into a values('A111',1113, 'Checking', '3/12/2005',5000)
> insert into a values('A111',1114, 'Savings', '4/2/2005',8000)
> insert into a values('A111',1115, 'Checking', '4/25/2005',2000)
> insert into a values('A111',1116, 'Savings', '3/25/2005',3000)
> insert into a values('A111',1117, 'Checking', '4/2/2005',5000)
> insert into a values('A111',1118, 'Savings', '3/12/2005',10000)
>
> --Ideal Output--
> referid description march_apps march_amt april_apps april_amt
> A111 Checking 2 9000 2 13000
> A111 Savings 2 15000 2 5000
select referid, description
,march_apps= SUM(case
when year(time)=2005 and month(time)=3
then 1 end)
,march_amt = SUM(case
when year(time)=2005 and month(time)=3
then amount end)
,april_apps= SUM(case
when year(time)=2005 and month(time)=4
then 1 end)
,april_amt= SUM(case
when year(time)=2005 and month(time)=4
then amount end)
From a
Group by referid, description
The numbers for the amount does not match the desired output, but
I think the erroe lies in the latter.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|