Solved

SELECT  Query

Posted on 2008-10-22
3
239 Views
Last Modified: 2013-12-07
I have column FY and Sales for a employee.
I wouldl like to get all the employees sales for FY and FY- 1 in one row. PLease see the snapshot attached for more clarity
query.bmp
0
Comment
Question by:chikku2003
3 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 200 total points
ID: 22777150
select empId, sum(case when fy='2008' then sales else 0 end) as Sale-Fy2008,
sum(case when fy='2007' then sales else 0 end) as Sale-Fy2007,
sum(case when fy='2006' then sales else 0 end) as Sale-Fy2006
from your_table
group by empid
0
 
LVL 6

Accepted Solution

by:
openshac earned 300 total points
ID: 22777220

CREATE TABLE #temp

( ID int, empID int, FY int, Sales int)
 

INSERT INTO #temp VALUES (1, 1, 2008, 10000)

INSERT INTO #temp VALUES (2, 1, 2007, 15000)

INSERT INTO #temp VALUES (3, 1, 2006, 20000)

INSERT INTO #temp VALUES (4, 2, 2008, 7500)

INSERT INTO #temp VALUES (5, 2, 2007, 2500)

INSERT INTO #temp VALUES (6, 2, 2006, 12500)

INSERT INTO #temp VALUES (7, 3, 2008, 1000)

INSERT INTO #temp VALUES (8, 3, 2007, 2000)

INSERT INTO #temp VALUES (9, 3, 2006, 3000)
 

SELECT empID, 

SUM(CASE WHEN FY = 2008 THEN Sales ELSE 0 END),

SUM(CASE WHEN FY = 2007 THEN Sales ELSE 0 END),

SUM(CASE WHEN FY = 2006 THEN Sales ELSE 0 END)

FROM #temp

GROUP BY empID
 

DROP TABLE #temp

Open in new window

0
 

Author Closing Comment

by:chikku2003
ID: 31508765
thats exactly i was looking at
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now