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
makhamI'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|||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|||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)|||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|||if the database is mysql, use the GROUP_CONCAT function
see, not all "grouping" has to be done on the client side :)|||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|||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|||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|||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 5879i'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|||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|||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 ;)|||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
