SQL Question

[Gunslinger08]

Okay, I have a query that's calling on several tables. I've attached the code I currently have.

The table cas_TesterAuthorizedSkillTest has 9 records for every record in cas_Tester. Is there anyway for me to make these 9 records columns in the dataset returned by this query? I'd like 1 line per tester, with all available information in that row.. but I'm not sure if that is even possible.

 

[mAdMaLuDaWg]

Don't quite understand what you are trying to do here... give some sample output.
Beyond that, why are you joining on same columns multiple times??? :
rgAdrIdContact = A.adrId AND rgAdrIdContact = A.adrId AND rgAdrIdContact = A.adrId
should be reduced to
rgAdrIdContact = A.adrId

 

[Gunslinger08]

Whoops, hadn't even noticed that enterprise manager had done that.

Say that T.tsrID is the primary key of the cas_Tester table (referenced as T). In the table cas_TesterAuthorizedSkillTest (referenced as S), there are 9 entries for each tsrID, each with a skill test name and a single bit saying whether or not they are authorized for that skill test. When I run my query, it is returning 9 rows for each unique tsrID; one for each skill test. I want to figure out how to condense this into one row, so that each skill test is listed as a column.

 

[Mark R]

The following is based on code which works in MS SQL server. I've kept it simple - you should be able to expand it to what you want to do.

I've assumed that tsrID is the individual person ID, and that S.tasSktID - is the ID number for a particular skill, and that S.tasAuthorizedInd is wheter that particular skill is authorized.

SELECT T.tsrID,
MAX (CASE WHEN (S.tasSktId = 1) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber1Authorized,
MAX (CASE WHEN (S.tasSktId = 2) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber2Authorized,
MAX (CASE WHEN (S.tasSktId = 3) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber3Authorized
FROM
as_Tester T INNER JOIN
cas_TesterAuthorizedSkillTest S ON T.tsrId = S.tasTsrId
GROUP BY T.tsrID

How it works:
You define a seperate column on the select statement for each skill
In each skill column use an case-then statement (equivalent to an 'if' statement) to select out only the data of interest.
Use a max() and a group by to compact your 9 records onto 1 line.
The cast statement is needed because MAX() cannot operate on bit types.

Note: Enterprise manager's query editor breaks once you introduce a 'case' statement into your query, so any futher editing will have to be done with SQL not the visual editor.

 

[Gunslinger08]

Awesome Mark, that does exactly what I want it to do.

Now, how would you recommend I tie this in with the previous query? Should I make your query a stored proc or a view? Or something else?

 

[Mark R]

I'd save the query in my post above as a view.

You can then modify the query in your original post to join with the view instead of the cas_TesterAuthSkillTest table.

 

[Gunslinger08]

Originally posted by: Mark R
I'd save the query in my post above as a view.

You can then modify the query in your original post to join with the view instead of the cas_TesterAuthSkillTest table.

Thank you very much Mark. You just saved me quite a bit of head scratching.

 

[statik213]

Originally posted by: Mark R
The following is based on code which works in MS SQL server. I've kept it simple - you should be able to expand it to what you want to do.

I've assumed that tsrID is the individual person ID, and that S.tasSktID - is the ID number for a particular skill, and that S.tasAuthorizedInd is wheter that particular skill is authorized.

SELECT T.tsrID,
MAX (CASE WHEN (S.tasSktId = 1) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber1Authorized,
MAX (CASE WHEN (S.tasSktId = 2) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber2Authorized,
MAX (CASE WHEN (S.tasSktId = 3) THEN CAST (S.tasAuthorizedInd as int) end) as SkillNumber3Authorized
FROM
as_Tester T INNER JOIN
cas_TesterAuthorizedSkillTest S ON T.tsrId = S.tasTsrId
GROUP BY T.tsrID

How it works:
You define a seperate column on the select statement for each skill
In each skill column use an case-then statement (equivalent to an 'if' statement) to select out only the data of interest.
Use a max() and a group by to compact your 9 records onto 1 line.
The cast statement is needed because MAX() cannot operate on bit types.

Note: Enterprise manager's query editor breaks once you introduce a 'case' statement into your query, so any futher editing will have to be done with SQL not the visual editor.

nice!