Transpose on unkown values...
I am making a database for a multilingual glossary. I have the following structure:
glossaries(name NVARCHAR PK, createdby NVARCHAR).
I also have a table for language codes
languages(code CHAR(3) PK).
I also have a table for the entries in the glossary:
entry(id int (pk), glossaryname (FK) languagecode(FK), entry (VARCHAR), headingentry int (fk - self join).
What I end up with is a long thin entry table, like this:
id glossaryname languagecode entry headingentry
--- --- --- --- ---
1 nature eng cow NULL
2 nature deu küh 1
3 nature fra vaiche 1
4 state deu heiss NULL
5 state eng hot 4
6 state fra chaud 4
(actually my database is a great deal more complicated than this, but this is the relevant part).
How do I transpose the values so that I get a report which looks like this:
ENG DEU FRA
--- --- ---
cow küh vaiche
hot heiss chaud
Even if I don't know beforehand which of over 300 possible languages have been used? Even if there are plenty of nonexistant rows or NULL entries...
The users do not want to enter data in a long thin sequential listing, they want it to look like Excel.