Pivot multiple columns

I have a table the records the results of three different tests that are graded on a scale of 1-7.The table looks something like this.

PersonIdTestATestBTestC

1454

2624

3556

4151

I would like to have a SQL statement that would pivot all this data into something like this

Test1234567

A1001110

B0100300

C1002010

Where the value for each number is a count of the number of people with that result.

The best solution that I have been able to come up with is to pivot each test and UNION ALL the results together.Is there a way to do this in a single statement?

(If this has already been covered I apologize, but I could not find the solution.)

Try:

use tempdb

go

Code Snippet

create table dbo.t1 (

PersonId int not null unique,

TestA int,

TestB int,

TestC int

)

go

insert into dbo.t1 values(1, 4, 5, 4)

insert into dbo.t1 values(2, 6, 2, 4)

insert into dbo.t1 values(3, 5, 5, 6)

insert into dbo.t1 values(4, 1, 5, 1)

go

;with unpvt

as

(

select

stuff(Test, 1, 4, '') as Test,

[Value]

from

dbo.t1

unpivot

(

[Value]

for [Test] in ([TestA], [TestB], [TestC])

) as unpvt

)

select

Test,[1], [2], [3], [4], [5], [6], [7]

from

unpvt

pivot

(

count([Value])

for [Value] in ([1], [2], [3], [4], [5], [6], [7])

) as pvt

order by

Test

go

drop table dbo.t1

go

AMB