Crosstab

Question

Post reply

Crosstab

Trybbe

Hall of Fame

Points: 3967
More actions
September 3, 2008 at 2:42 am

#193109

HI! I need help with the results of this query.
The query looks like this:
SELECT headcountdec.StaffNo, headcountdec.CallName, headcountdec.Surname, headcountdec.BranchID, headcountdec.Branch, jpRoles.Role,
jpOutputs.Output, jpOutputs.Weight, jpOutputs.Activities
FROM jpRoles INNER JOIN
jpProfileRoles ON jpRoles.RoleID = jpProfileRoles.RoleID AND jpRoles.period = jpProfileRoles.Period INNER JOIN
headcountdec INNER JOIN
Positions ON headcountdec.PosID = Positions.PosID AND headcountdec.Period = Positions.Period ON jpProfileRoles.ProfileID = Positions.ProfileID AND
jpProfileRoles.Period = Positions.Period INNER JOIN
jpOutputs ON Positions.ProfileID = jpOutputs.ProfileID AND Positions.Period = jpOutputs.Period
WHERE (headcountdec.headcount = 'headcount') AND (headcountdec.StaffNo = 123123) AND (headcountdec.Period = 200807)
-------------------------------------------------------------------------
I've attached the spreadsheet with the results of that query:
The top set is how I get the results when I run this query.
The results below is how I want the results to look like.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

j nuñez SSCrazy Points: 2115 More actions · Answer 1

we need more information to help you.

What logic are you using to decide how group/select the information you present in the excel file?

Can you explain the relationship between the tables and the meaning of the fields?

The most information you put, the better we can help you.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Check that this matches the query you posted, which was a little garbled:

[font="Courier New"]SELECT headcountdec.StaffNo, headcountdec.CallName, headcountdec.Surname, headcountdec.BranchID, headcountdec.Branch,

jpRoles.Role,

jpOutputs.Output, jpOutputs.Weight, jpOutputs.Activities

FROM jpRoles

INNER JOIN jpProfileRoles

ON jpRoles.RoleID = jpProfileRoles.RoleID AND jpRoles.period = jpProfileRoles.Period

INNER JOIN Positions

ON jpProfileRoles.ProfileID = Positions.ProfileID AND jpProfileRoles.Period = Positions.Period

INNER JOIN headcountdec

ON headcountdec.PosID = Positions.PosID AND headcountdec.Period = Positions.Period

INNER JOIN jpOutputs

ON Positions.ProfileID = jpOutputs.ProfileID AND Positions.Period = jpOutputs.Period

WHERE (headcountdec.headcount = 'headcount')

AND (headcountdec.StaffNo = 123123)

AND (headcountdec.Period = 200807)[/font]

Then try the following:

1. Use the DISTINCT clause

2. Use a GROUP BY

3. Identify the table which messing up the cardinality: the easy way to do this is to comment out tables one at a time from the query. Then instead of joining to the table, join to a derived table returning the values you want (and the values required for the joins)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Trybbe Hall of Fame Points: 3967 More actions · Answer 3

Hi! the structure is like this.....

Table relations.

Headcountdec -- Contains headcount info. eg. Staffno, callname, surname and branchdetails

Positions -- Contains position details but for this query it has a field called profile ID which I need to link to profiles.

jpOutputs -- job profile outputs and links to position table via profileid.

jpProfileRoles -- I only use this table as a link between positions table and jpRoles. It only has three fields, profileid and roleid.

jpRoles -- Thats where we get the roles associated to the job profiles.

Now the problem is between roles and outputs because most of the time roles are either equal or lesser than outputs and the problem arises when outputs are more than roles then your roles field will return three values and outputs will return more than three values. That's when my data get messed up. Hence I'm trying to get the result output in a crosstab format

I hope this is explanatory enough

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

The spreadsheet sample of how you want your data to look shows one output per role, and one output not associated with any role.

What is your logic for deciding which output should be associated with which role?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

j nuñez SSCrazy Points: 2115 More actions · Answer 5

Can you put an example of your data in your tables, only the information releted to the case showed in the excel file?