Dislplay Row Data as Columns?

[PEJO]

I have a table that looks something like this

USERID TEST_ID Score
User_1 Test_1 78
User_1 Test_2 56
User_1 Test_3 88
User_2 Test_1 90
User_2 Test_2 92
User_2 Test_4 88
User_2 Test_1 75
User_3 Test_2 58
User_3 Test_4 100

I want it to look like this:

USERID Test_1 Test_2 Test_3 Test_4
User_1 78 56 88
User_2 90 92 88
User_3 58 100

--

 

[Allen Browne]

Looks like a classic crosstab query.

You can use the wizard to create a crosstab, or just start a query and
change it to Crosstab on the Query menu.

UserID will be the Row Heading.
Test_ID will be the Column Heading
Score will be the value.

You must aggregate the score, so perhaps choose First in the Total row.

 

[pottymouthed]

Is there a way to do it codematically IE through SQL. I don't think
I'm going to have access to the db.

 

[Allen Browne]

Sure.

If you can borrow an Access machine, you can get the SQL statement by
switching to SQL View after you make the query.

If you must write the query statement by hand, look up help on TRANSFORM.
There's a sample statement in this article:
http://support.microsoft.com/kb/210004

 

[pottymouthed]

THanks I got it...I just rebuilt the table locally and used the wizard
and grabbed the code.