Translating a table

[cbende2]

Hey guys, I'm stumped right now.... I'm trying to translate a table for display reasons.



My original table is as follows:
EDIT: for better understanding
Ok guys, So I only have 1 table, TableA which is below

ID	CourseNumber	Outcome
1	1	a
2	2	a
3	2	b
4	3	c
5	4	b
6	1	d

And I'm trying to translate TableA into a temporary table that will look like this:

CourseNumber	a	b	c	d
1	X			X
2	X	X
3			X
4		X

An "X" represents the existence of that record from TableA... So since I have a record 1a, an "X" is marked in the 'a' column.

How would I go about producing this table?

Is there a query that would display the result set in the fashion I would like? Or will I have to make an array of some sort and fill it? I'm stumped!


Thanks,

cbende2

[orange]

Not sure if this is relevant or helpful/
My first reaction is a table

TransTbl

ID (whatever that signifies)
Letter

with records like

1a
1d
2a
2b
3c
4b

Much like a junction table.

Good luck.

EDIT: Jumped in too quickly --- was thinking of storage not display.

[CJ_London]

use a crosstab query - that will give you a-d across the top. With regards 1-4 rows they will only appear providing there is at least 1 'x'.

# would be your row heading, letter your column heading and a X as the value (set to first). Thou I hoppe those aren't your real field names

[cbende2]

Please check my post edit. I'm trying to code this in PHP so I don't think a crosstab is possible. Also, any suggestions do not have to be in PHP. Simple psuedo-code will work.

[cbende2]

Found answer here: http://stackoverflow.com/questions/7...ql-pivot-table