SQL SYNTAX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL SYNTAX

Author

Topic

faruk22
Starting Member

3 Posts

Posted - 2010-07-09 : 09:34:05

Hi,

I'm developing an accounting program. SQL table is so;

Year Person Amount
2008 Alex 100
2009 Alex 40
2010 Alex 0
2008 Jennifer 50
2009 Jennifer 130
2010 Jennifer 200

But i wanna a report like this.

Person 2008 Amount 2009 Amount 2010 Amount
Alex 100 40 0
Jennifer 50 130 200

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-09 : 09:44:47

this might help..


Select person,
case when [Year]=2008 then Amount else 0 end as [2008],
case when [Year]=2009 then Amount else 0 end as [2009],
case when [Year]=2010 then Amount else 0 end as [2010]
from 
<your table>

A maze make you much more better

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-09 : 10:02:03

Select person,
sum(case when [Year]=2008 then Amount else 0 end) as [2008],
sum(case when [Year]=2009 then Amount else 0 end) as [2009],
sum(case when [Year]=2010 then Amount else 0 end) as [2010]
from
<your table>
group by
person

Madhivanan

Failing to plan is Planning to fail

faruk22
Starting Member

3 Posts

Posted - 2010-07-09 : 10:06:47

thx. but it results so;

Person 2008 Amount 2009 Amount 2010 Amount
Alex 100 0 0
Alex 0 40 0
Alex 0 0 0
Jennifer 50 0 0
Jennifer 0 130 0
Jennifer 0 0 200

Sachin.Nand

2937 Posts

Posted - 2010-07-09 : 10:10:01

quote:
Originally posted by faruk22

thx. but it results so;

Person 2008 Amount 2009 Amount 2010 Amount
Alex 100 0 0
Alex 0 40 0
Alex 0 0 0
Jennifer 50 0 0
Jennifer 0 130 0
Jennifer 0 0 200

Post the actual query which you are using.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-09 : 10:13:53

quote:
Originally posted by faruk22

thx. but it results so;

Person 2008 Amount 2009 Amount 2010 Amount
Alex 100 0 0
Alex 0 40 0
Alex 0 0 0
Jennifer 50 0 0
Jennifer 0 130 0
Jennifer 0 0 200

See my first reply

Madhivanan

Failing to plan is Planning to fail

faruk22
Starting Member

3 Posts

Posted - 2010-07-09 : 10:21:44

yes it works.
THX.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-09 : 10:44:13