Dynamic Crosstab Query - help needed.

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

General SQL Server Forums

New to SQL Server Programming

Dynamic Crosstab Query - help needed.

Author

Topic

Shaheen
Starting Member

1 Post

Posted - 2009-06-03 : 11:36:28

Hello Guys,

I am very green to the SQL world, though I suppose I have sufficient amount of experience in the ACCESS. Sometimes back, I have designed this human resources database for the organization that i work.. now it seems like I have to migrate it SQL Server 2005, for the sake of multiple users access to the database.

I have managed to migrate it, but I have a bit of problem with my Crosstab Queries. . they are not upsize-able to the SQL 2005 Server. I went around the net to search for some help, but it seems like I don't get anything out it. Normal, since I don't have much clue of SQL World. I was wondering if someone could help me out with this issue.

Here is my first crosstab query that needs to be designed in SQL Server. I have a view which obviously has many fields and many records. I would like to make a cross-tab query out of fields (section and gender, filtered by Status of Employment). This will then show how many male and female is currently working under each section (or other words, Department of the organization). I would like the query to work off values that exist in the view and not out off fixed value, though gender is pretty much fixed:-)

Here is my access SQL Code:

TRANSFORM Count(qryemployees.[Employee Name]) AS [CountOfEmployee Name]
SELECT qryemployees.section
FROM qryemployees
WHERE (((qryemployees.Status)="on board" Or (qryemployees.Status)="new arrival" Or (qryemployees.Status)="under repatriation"))
GROUP BY qryemployees.section
PIVOT qryemployees.Gender;

Here is my access query datasheet view:

Section Female Male
Administrative Services (AS) 23 40
Democratic Institutions & Human Rights 40 84
Integrated Support Services (ISS) 58 318
O/SRSG & DSRSG 9 12
Office of Deputy Director (DDMS) 8 7
Office of the Director (DMS) 1 0
Security 1 3

Your help will be highly appreciated.

Shaheen

Shaheen,
United Nations Volunteer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 11:47:34

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources