Pivoting test results with SQL Server

Question

Pivoting test results with SQL Server

benjones62

SSC Rookie

Points: 31
More actions
October 5, 2009 at 8:30 pm

#142277

I am trying to pivot counts of test results from a table into columns.
My table is structured as such:
vw_detailResults
clientid
testid
scoreid
testdate
cityid
countyid
with a lookup table for testid to test name (tbl_tests)
MY CURRENT SELECT STATEMENT
SELECT TestDescription,
[-1] as NA,[11] AS B, [1] as [1], [2] as [2], [3] as [3], [4] as [4], [5] as [5], [6] as [6],
[7] as [7]
FROM (
SELECT a.TestID, b.TestDescription, score FROM vw_DetailResults a
INNER JOIN tbl_tests b on b.TestID = a.testid
Where cityid in (249684732) and CountyID in (157589)and
TestDate between '1/2/2006' and '5/22/2009'
Group By a.testid, TestDescription,score
) o
PIVOT (COUNT(score) FOR score IN ([-1],[11],[1],[2],[3],[4],[5],[6],[7])) p
Results
TEST V1 V2 V3 V4 V5 V6 V7 V8 V9
------------------------------------------
TEST1100011111
TEST2000001000
TEST3000000100
TEST4100011110
TEST5100011110
TEST6100001111
TEST7100011100
When I run my Inner Select I get
TESTDESCRIPTIONCOUNTSCOREID
TEST4EXAMPLE 6-1
TEST4EXAMPLE 143
TEST4EXAMPLE 584
TEST4EXAMPLE 615
TEST4EXAMPLE 146
The results of the main select should be for this testid:
TEST46001458611400
What am I missing to get the proper counts in the values above? Am I structuring my pivot correctly, or is there a better way to do this?
As always, any help is greatly appreciated!

Viewing 3 posts - 1 through 2 (of 2 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You'll probably get a better response if you use the [ code ] tags and try to format things a little:

benjones62 (10/5/2009)
I am trying to pivot counts of test results from a table into columns.
My table is structured as such:
vw_detailResults
clientid
testid
scoreid
testdate
cityid
countyid
with a lookup table for testid to test name (tbl_tests)
MY CURRENT SELECT STATEMENT
SELECT TestDescription,
[-1] as NA,[11] AS B, [1] as [1], [2] as [2], [3] as [3], [4] as [4], [5] as [5], [6] as [6],
[7] as [7]
FROM (
SELECT a.TestID, b.TestDescription, score FROM vw_DetailResults a
INNER JOIN tbl_tests b on b.TestID = a.testid
Where cityid in (249684732) and CountyID in (157589)and
TestDate between '1/2/2006' and '5/22/2009'
Group By a.testid, TestDescription,score
) o
PIVOT (COUNT(score) FOR score IN ([-1],[11],[1],[2],[3],[4],[5],[6],[7])) p
Results
TEST V1 V2 V3 V4 V5 V6 V7 V8 V9
------- ---- ---- ---- ---- ---- ---- ---- ---- ---
TEST1100011111
TEST2000001000
TEST3000000100
TEST4100011110
TEST5100011110
TEST6100001111
TEST7100011100
When I run my Inner Select I get
TESTDESCRIPTIONCOUNT SCOREID
TEST4EXAMPLE 6-1
TEST4EXAMPLE 143
TEST4EXAMPLE 584
TEST4EXAMPLE 615
TEST4EXAMPLE 146
The results of the main select should be for this testid:
TEST46001458611400
What am I missing to get the proper counts in the values above? Am I structuring my pivot correctly, or is there a better way to do this?
As always, any help is greatly appreciated!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

It would also help if the documented code matched the documented results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)