Microsoft SQL Server forum - Query Help, thanks!

Author

Query Help, thanks!

rong.guo@gmail.com

2005-05-27, 8:23 pm

Hello group!

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

--My Query--
select referid, description
,march_apps=case
when year(time)=2005 and month(time)=3
then count(app_id) end
,april_apps=case
when year(time)=2005 and month(time)=4
then count(app_id) end
>From a
Group by referid, description, time

MGFoster

2005-05-27, 8:23 pm

rong.guo@gmail.com wrote:
> Hello group!
>
> 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
>
> --My Query--
> select referid, description
> ,march_apps=case
> when year(time)=2005 and month(time)=3
> then count(app_id) end
> ,april_apps=case
> when year(time)=2005 and month(time)=4
> then count(app_id) end
> Group by referid, description, time
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your CASE expressions should be like this:

,march_apps = COUNT(CASE WHEN Year(time)=2005 AND Month(time)=3
THEN app_id ELSE NULL END)
,april_apps = COUNT(CASE WHEN Year(time)=2005 AND Month(time)=4
THEN app_id) ELSE NULL END)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/ AwUBQpeEfIechKqOuFEg
EQK/ UACg6KTx0s1buMshqn2p
PecgCoVo/AQAoJkB
frb/qM3Q2KCqoqsMlP/k59gO
=rsrt
-----END PGP SIGNATURE-----

Erland Sommarskog

2005-05-27, 8:23 pm

(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/techin.../2000/books.asp

Sponsored Links