Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Suburban Maryland
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab queries in SQL (Access 2000)

    Do you know where there is an introduction to writing Crosstab queries in SQL? Since learning SQL I really prefer it to the other approaches to making a query. I can't find any kind of definition for the behavior of the key words "TRANSFORM" and "PIVOT".

    Help is helpless and I don't find anything at the Microsoft Support site. Perhaps I have not searched correctly but probably I have.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Crosstab queries in SQL (Access 2000)

    Why not just use the Crosstab query wizard?
    If you then want to find out what the wizard has done just view the query in SQL mode.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab queries in SQL (Access 2000)

    Are you talking about using SQL Server, or using the SQL view in an Access query?

    If you mean SQL Server, I haven't found an easy way to recreate the PIVOT functionality that's supported only in Access (at least not with SQL Server 7.0).

    AFAIK, your best bet is to pull the SQL Data into Access (either link or import), then do the Crosstab there.

    HTH

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Suburban Maryland
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab queries in SQL (Access 2000)

    That's what II've done. But for a variety of reasons (one of the most important of which is a persistent itch to be good at what I do and to understand, as far as it is possible, what the software is doing), I want to do it in SQL. Another reason is I consistently find I can do things in SQL that Query Design View can't do, doesn't even know exist. My main project has a lot of tables and a lot of relationships, for instance, and I'm forever having to come up with new kinds of multiple JOIN clauses. It works, but I can't do it, or a lot of stuff, in QDV.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Crosstab queries in SQL (Access 2000)

    I admire your tenacity, but, what I find is that after using a wizard and going into the SQL generated I can also learn a lot as well as experimenting with the SQL.
    I would wait for Wendell or Charlotte or a number of others to awake, as I'm sure they will know of good sources.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab queries in SQL (Access 2000)

    Actually, there's very little that you can't do in the query design view. Union queries, passthrough and data definition queries can't be built there, plus you still have to use SQL to build subqueries and to create a from clause on a subquery, but that's about it. In my experience complex joins are a LOT easier to build visually than by writing the SQL for them. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> If you're looking for general SQL references, O'Reilly has SQL in a Nutshell, which gives you the syntax for several SQL versions, including SQL Server (T-SQL), MySQL, and Jet SQL, but for real nitty gritty stuff, get one of Joe Celko's books and study it.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab queries in SQL (Access 2000)

    There is an MSKB article that might help, Mark: HOW TO: Rotate a Table in SQL Server. The most straightforward way is to use the SQL Server Analysis Services or OLAP services, at least in SQL 2000. I never tried the analysis services in SQL 7, but I believe the pivottable stuff is new to 2000. You could build OLAP cubes in 7, though.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab queries in SQL (Access 2000)

    Mark,

    Here'san article on Upsizing Jet Crosstab Queries, if that helps.
    Charlotte

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab queries in SQL (Access 2000)

    Thanks, Charlotte. That's a very helpful article!

    I came across a situation that needed this type of functionality. I explored the use of Cubes, OLAP, and I even found the MSKB article you mentioned above. My problem is that the number of column headings needed to be dynamic - unlike summarizing months or quarters. (I was working with a Survey, summarizing the number and type of responses.)

    Once I realized that SQL Server doesn't support PIVOT or TRANSFORM I used an Access ADP to gain the PIVOT functionality.

    I'll keep this article for future reference.

    Thanks again <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

Posting Permissions

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