SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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)
    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...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by asterix
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •