Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Unanswered: Expected Formatted Results??

    Hi All,
    I am kindly seeking for help.
    I have a table(MyTable) which is defined as (date datetime, ID char (10), and R, P,M,D&Y are all float) and the layout is as following:
    Date ID R P M D... Y
    1/1/90 A 1 2 3 4... 5
    1/2/90 A 2 3 4 5... 1
    ...
    2/11/05 A 3 4 5 6... 2
    1/1/90 B 1 2 3 4... 5
    1/2/90 B 2 3 4 5... 1
    ...
    2/11/05 B 3 4 5 6... 2
    ...
    The expected query results look like: ( this results from Date, ID and R fields)
    Date A B
    1/1/90 1 1
    1/2/90 2 2
    ...
    2/11/05 3 3

    The SQL I wrote:
    select date, ID,
    A=sum(case when ID=A then R else 0 end),
    B=sum(case when id=B then R else 0 end)
    from MyTable
    Group by date

    I would also like to get another set of results with the same format but from date,ID and P fields:
    Date A B
    1/1/90 2 2
    1/2/90 3 3
    ...
    2/11/05 4 4

    select date, ID,
    A=sum(case when ID=A then P else 0 end),
    B=sum(case when id=B then P else 0 end)
    from MyTable
    Group by date

    The problem with that is if I have thousands of ID in MyTable I have to "hard code" thousands times and the same problem with the fields/columns. Is there any easier way to do this?
    I also would like to insert the results into a table/view which will be refreshed whenever MyTable gets updated.

    Any suggestion/comments are highly appreciated!
    shiparsons

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For the moment you have to do it this way, unless you want to get into hairy dynamic SQL.

    And anyway, if you do not hard-code the column names in your crosstabs then good luck creating any reports based upon them (Crystal, Access, or anything else). Reporting applications tend to require advance knowledge of the data layout they use...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    88
    Not because I do not trust your comments...I am not sure if I know SQL well enough to make a judgement. Is there another way to go around? Could you please elaberate "hairy Dynamic SQL"?

    Many thanks,
    shiparsons

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree with blindman, this problem is practically impossible to solve on the server. However, there are many, relatively simple solutions to this problem on the client side.

    Depending on what tool you choose to use, the wording and methods are slightly different. Excel has pivot tables, MS-Access has crosstab reports, etc. Most client analysis tools support this type of activity in some way.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hairy dynamic SQL, meaning you will have to access the system tables or SCHEMA tables to locate the columns for your table, and then loop through each one of them constructing your SQL statement as a string before executing it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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