Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Unanswered: Dynamic Reverse Crosstab

    If I have a table that looks something like this:

    ID E01 E02 E03 E04 E05
    1 1000 200 150 350 140
    2 800 150 120 670 356

    How would I turn it into this?

    ID Code Amount
    1 E01 1000
    1 E02 200
    1 E03 150
    1 E04 350
    1 E05 140
    2 E01 800
    2 E02 150
    2 E03 120
    2 E04 670
    2 E05 356

    I know I could do a series of subqueries and put them together with a union query, but I have to make up each subquery individually for each field in the table. The field names change often, so I'd prefer something that can dynamically read the field names and use them in the code field.

    Any ideas?


  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    I am of the view that unfortunately this can not be solved dynamically per se, for the columns that would be "dynamic" can only be referenced in a static context.

    This example serves to firmly reinforce why correct database design methods should be adhered, to avoid issues exactly like this. Had the table been developed to 1NF, for this form would have sufficed to solve the problem you describe, the solution could have been delivered with a simplistic cross-tab query or variation thereof. In particular, the support for CASE expressions in SQL Server and MS Access allows for powerful queries of this kind to be developed quickly and to perform very efficiently.

    With the table design that you have, however, I can only agree with your reluctant proposal to develop each column to row transformation manually and to amalgamate the results with the UNION operator.

    You may want to consider creating a view of this result set to ease in the development of future queries that need to either return a view of this result set, or use it in the construction of another query.

    The following comment is not directed directly at the original poster.

    It is rather alarming that today, with the continuous supply of examples of ill-designed database schemas that inadvertently result in the development of unnecessarily complex and impractical solutions to query the data, that the importance of understanding the theory and principles of database design and querying principles is all too often undervalued.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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