Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: Pivot table (was "help")

    i have table like


    id charge
    10 9921
    10 4152
    10 5879

    i want to display this date in one row like

    id charge charge charge
    10 9921 4125 5879


    and i have thousand of record ,which i want to display in this pattern

    thanks
    makham

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've moved your posting to a new forum, it seems more likely to attract comment in the SQL forum than the New Users and Introductions forum.

    How many rows of data are you using, and how many repeating "charge" values? That will make a huge difference in how you approach a solution.

    A pivot should really be done by the client, rather than being done by the database server. The client side has tools that are better suited to this kind of task, as well as more direct contact with the user.

    -PatP

  3. #3
    Join Date
    May 2006
    Posts
    2
    i have thousands of rows and charges repeat up to 10 (that is i can have 10 charges value for a single id) an whats about new forum .and how i can go there.

    thanks
    makham

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this sounds more like a user interface problem rather than a SQL problem

    the sql should be fairly straightforward, its a grouping or sub select on first glance

    Before we dive into detail SQL statements, what ius you user interface (ie how are you presenting this information to a view (using VB, VC, .NET, Web page or Access)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're already in the "new" forum. I moved the thread, but left a "tail" on it that you just "automagically" follow when you clicked on it.

    Since HealdM agrees with me, we're 2 for 2 in thinking that you should really handle the grouping on the client side, not at the server. What kind of client side software do you have, because knowing that will make the final choice much easier to make.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the database is mysql, use the GROUP_CONCAT function

    see, not all "grouping" has to be done on the client side
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, you can do grouping on the server using a SQL server just as well as you can with MySQL. My comment was that grouping should not be done on the server, not that grouping could not be done there.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you suggesting that someone using MySQL should avoid using the GROUP_CONCAT function?

    because that's sure what it sounds like you're saying
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When using the GROUP_CONCAT function makes sense, go for it.

    Application design is always a balancing act. There is almost never just one way to do something. You have to consider what you are doing, and why you are doing it to know what is the best solution for you in a given circumstance.

    If you are producing a static text report, that will always be in one form (completely "cold", not interactive in any way), then GROUP_CONCAT could be a good choice, especially for a pure two-tier environment. If you have application servers involved, or if your user interface is interactive (to the extent that grouping might change), then GROUP_CONCAT probably isn't a good choice because it will force additional round-trips to the app or database server.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by makham
    i have table like


    id charge
    10 9921
    10 4152
    10 5879

    i want to display this date in one row like

    id charge charge charge
    10 9921 4125 5879
    i'm having a hard time seeing how this grouping might change, pat, or how GROUP_CONCAT will force additional round-trips to the app or database server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, if I fell into the logical trap of assuming that the posted example was exactly what was being run instead of an example, I'd probably come to the same conclusion. As this example is quite generic, and has nothing to make me think that it is the actual problem, I'm nearly certain that it is simply a snippet of contrived code to give us an idea of what the poster is doing.

    In the real world, this kind of problem usually is much more complex because it returns a half dozen or more columns that can be used for grouping, and the UI almost always allows the end user to change the grouping... That is what I was referring to that would cause extra round trips.

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Yes, if I fell into the logical trap of assuming that the posted example was exactly what was being run instead of an example, I'd probably come to the same conclusion. As this example is quite generic, and has nothing to make me think that it is the actual problem, I'm nearly certain that it is simply a snippet of contrived code to give us an idea of what the poster is doing.
    well, i tell you what -- i'm going to continue to answer the questions that are actually asked, as asked, and you can go ahead and answer whatever you think the actual problem might be

    perhaps we'll see each other in the same thread again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As we discussed offline, if the user is going to run that query, exactly that query, and only that query, then you and I agree that there is no harm (and actually minor benefit) in doing a pivot on the server.

    I strongly believe that the pivot tools on the client are much better than pivot tools on the server. I also think that the query posted was only an example, and that other columns are probably involved, which makes the scenario I envisioned (with the potential for repeated trips to the server to re-pivot the data) much more likely.

    If all of your assumptions hold true, then I agree with you. In tens of thousands of cases that I've seen in almost 30 years of programming, those assumptions probably would hold true in about five of the cases. I'm not willing to bet that this is another of them.

    -PatP

Posting Permissions

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