SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Transpose on unkown values...
-
Feb 27, 2005, 13:49 #1
Transpose on unkown values...
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
-
Feb 27, 2005, 13:58 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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)
-
Feb 27, 2005, 14:19 #3
Originally Posted by r937
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...
-
Feb 27, 2005, 14:30 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by asterix
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
Bookmarks