Group by query

I have this table ....

AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC

1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR

1002 ZX 1/1/2006 21 DR

1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC

Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006

AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21

--
1003 ABC 23 0 0

One row for each name,that is group up by will be on AID and NAME.

Any help will be greatly appreciated...create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.|||create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.
Sorry ,I think that will not work at all.I mean if you use case in that case you have to use group by on AID,NAME,Type.
But That will give you multiple rows, which I don't want...
I think its not so easy man... ;)|||I think its not so easy man...
yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!|||yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME
:rolleyes: Oh my God !! Yes thats it ...I haven't thought that ...:rolleyes:
If you put the sum within the case, you have to use Type in Group by...hmmm thats it
Superb !!
You have put the case within Sum(), WOW!!
Rudy you are great !!|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!

Anyways, Thanks Wash for your help.:)