Transpose on unkown values...

[asterix]

Hi,

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.

Thanks

[r937]

use a crosstab query in microsoft access

crosstabs with an indeterminate number of columns is pretty difficult with sql

(note: "pretty difficult" being an understatement)

however, the access crosstab facility is not part of its database engine, but rather, part of its front-end capability

you can also do it with a scripting language (e.g. http://r937.com/crosstab.html -- not one of my better or easy-to-understand articles)

[asterix]

use a crosstab query in microsoft access

crosstabs with an indeterminate number of columns is pretty difficult with sql

(note: "pretty difficult" being an understatement)

however, the access crosstab facility is not part of its database engine, but rather, part of its front-end capability

you can also do it with a scripting language (e.g. http://r937.com/crosstab.html -- not one of my better or easy-to-understand articles)

Thanks, nice article.

The scripting approach doesn't look like its going to have anywhere near acceptable performance...

I tried an Access Crosstab, but I couldn't get it to worl Anyway, I hardly want to distribute an Access Front end with my SQL Server DB....

I guess I'll just have to denormalize, and limit the maximum number of languages in a row to 16 or something...

[r937]

The scripting approach doesn't look like its going to have anywhere near acceptable performance...

what makes you say that?

the sql is simple and straightforward, and the only thing that might slow it down is the fact that it requires an ORDER BY

you're saying your database isn't going to have acceptable performance with an ORDER BY? because the scripting will take picoseconds

by the way, i would love to see the front end that you deliver to users that has 16 TEXT fields side by side -- could you mock up an example for me?

or else i don't understand what you meant by --

The users do not want to enter data in a long thin sequential listing, they want it to look like Excel

in my opinion, it's going to be long and thin no matter whether it's horizontal or vertical