Problem with query- Will PIVOT help? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Problem with query- Will PIVOT help?

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 01:48:32

Hi all,

I have to write a query to fetch the details of a particular project.
The columns i want are:
Project_name,Manager name and the details about skills of the members of the project.
Like i want the count of people on JAVA, DB,.Net etc.

Now the skill sets are in the details table.
how to fetch them so tht i get them in a single row?

My o/p must look somewht like this:

PROJECT_NAME | Manager | Total |.NET |JAVA |DB | OTHERS
ABC | XYZ | 12 | 3 | 2 | 4 | 3

of these, project_name, manager_name and total comes from project table and the rest of the details about the skills come from details table.

Please suggest me how to write a query....Its urgent....
One of the suggestions was to use PIVOT for fetching the skill details. How to do tht?

Pls help....
Thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 01:54:04

Are you skill list static? will it be always .NET,Java,DB & OTHERS? If yes, post your structure off tables.Also PIVOT operator is available only on SQL 2005 and above versions.

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 02:04:07

Hi visakh,
thanks for the reply.
yes, they are almost static. there are about 10-12 skills in tht table.
I need the count of resources on each skill who are working on the project choosen.
is there any way by which we can make the query reusable so tht we need not worry if few more skills are added?

i have begun with the query,here it is ....

select p.prj_seq_no,p.prj_project_name,e.emp_name Manager
count(rra.emp_seq_no) as Total,
--- after this i want those skill columns....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 02:12:43

suppose if your tables are

Project
-------------
Projectid
ProjectName
Manager

and

Details
---------
Detailid
Projectid
MemberName
Skillset
...

then use like below

SELECT p.ProjectName,
p.Manager,
d.[.NET]+d.[JAVA]+d.[DB]+d.[OTHERS] AS Total,
d.[.NET],d.[JAVA],d.[DB],d.[OTHERS]
FROM Project p
INNER JOIN 
(SELECT Projectid,
 SUM(CASE WHEN Skillset='.NET' THEN 1 ELSE 0 END) AS [.NET],
 SUM(CASE WHEN Skillset='JAVA' THEN 1 ELSE 0 END) AS [JAVA],
 SUM(CASE WHEN Skillset='DB' THEN 1 ELSE 0 END) AS [DB],
 SUM(CASE WHEN Skillset NOT IN ('.NET','JAVA','DB') THEN 1 ELSE 0 END) AS [OTHERS]
FROM Details
GROUP BY Projectid)d
ON p.Projectid=d.Projectiid

if you want to make it reusable you need to use dynamic sql.

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 03:54:20

hi
can i include few more join conditions just before the subquery?
because i have to query 3 tables to fetch the project and manager names.....

also, the detail table has to be joined with a skill master to get the names of the skills...
how to do it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 04:15:05

quote:
Originally posted by mrm23

hi
can i include few more join conditions just before the subquery?
because i have to query 3 tables to fetch the project and manager names.....

also, the detail table has to be joined with a skill master to get the names of the skills...
how to do it?

you can. post your table structures with some sample data if you need detailed soln.

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 05:12:36

Here are the details:

the tables which i am using are :
TABLE | COLUMN
Project | project_id,project_name
Employee | Manager name
Resource | count of the resources in a particulare project
detail | holds the skill id
skill_code | to find the corresponding skill
****************************************************************************
with these i want to extract :
project_name,manager_name,count(resources),count(res_on_JAVA),count(res_on_DB)....etc

there are totally 12 skills. i want individual count of employees on all the skills.
-------------------------------------------------------------

This is my query:

select p.prj_seq_no a ,p.prj_project_name,e.emp_name as "PM/PL",e1.emp_name as GM,
count(rra.rra_billable) as billed
from rpmg_resource_allocations rra
inner join project p on p.prj_seq_no = rra.prj_seq_no
inner join sys_business_code_detail sb on sb.sbd_item_code = p.prj_status_item_code
and sb.sbm_type_code = 'SZ' and sb.sbd_item_desc not in ('Completed','Scrapped')
inner join employee e on e.emp_seq_no = p.prj_pm_seq_no or e.emp_seq_no = p.prj_pl_seq_no
inner join employee e1 on e1.emp_seq_no = p.prj_gm_seq_no
where rra.rra_status = 1 and rra.rra_billable != 0
group by p.prj_seq_no,p.prj_project_name,e.emp_name,e1.emp_name

***************************************************************************************
now, i need to include the skill sets also...how to do?
This query is working fine now. I want to know how to enhance the query for the count on skills

-------------------------
let me know if u need any other info
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:17:57

does detail table hold employeeid or resource info? how do you link skill to employee or resource?

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 05:22:00

no the skill ids are stored in emp_detail. their descrpition are stored in sys_business_code_detail.(sb)
the relation is like :
emp_detail.eed_primaryskills = sb.sbd_item_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:31:33

quote:
Originally posted by mrm23

no the skill ids are stored in emp_detail. their descrpition are stored in sys_business_code_detail.(sb)
the relation is like :
emp_detail.eed_primaryskills = sb.sbd_item_code

which table holds the resource deatils? employee or rpmg_resource_allocations? why are you taking count(rra.rra_billable) as billed and not from employees table?

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 05:39:32

rpmg_resource_allocations gives the count of the employees in a particular project.
i want to count from tht table for the chosen project so tht i will get the exact count of those who are allocated to tht project.

also, the project table is not direclty linked to employee.
it holds only the emp_id of the manager of the project.

so, to know the members of the proj, we need to depend on rpmg_resource_allocations.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:52:30

quote:
Originally posted by mrm23

rpmg_resource_allocations gives the count of the employees in a particular project.
i want to count from tht table for the chosen project so tht i will get the exact count of those who are allocated to tht project.

also, the project table is not direclty linked to employee.
it holds only the emp_id of the manager of the project.

so, to know the members of the proj, we need to depend on rpmg_resource_allocations.

how is that relation existing? what are columns that are related?

mrm23
Posting Yak Master

198 Posts

Posted - 2008-09-25 : 06:20:06

via project id prj_seq_no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:56:16

quote:
Originally posted by mrm23

via project id prj_seq_no

i'm still not getting relationship correctly. would appreciate if you could give some sample data from your tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources