1

Not sure how to phrase the question really, but here's what I have and here's what I need.

I've got a table that looks like this:

Name             K%      Year
Albert Pujols   7.90%   2006
Albert Pujols   8.50%   2007
Albert Pujols   8.40%   2008
Albert Pujols   9.10%   2009
Albert Pujols   10.90%  2010
Albert Pujols   8.90%   2011
Albert Pujols   11.30%  2012

I'd like to create a query that will produce output that looks like:

Albert Pujols   7.90%   8.50%   8.40%   9.10%   10.90%  8.90%   11.30%

While this particular player has 7 rows, I can't be guaranteed that such will exist.

Is this even possible?

I'd appreciate any help. I wouldn't have any trouble if I knew that there were only 2 rows (inner join on name)... but the variable number of rows is throwing me for a loop.

Edit**

Peter Wooster's answer of pivoting was the solution I needed.

Anthony Tyler
  • 157
  • 2
  • 11
  • 1
    what you are looking for is to "pivot" the data, there are tons of answers here about that. – Peter Wooster Jan 20 '13 at 02:38
  • Do you want to just group them based on the Name and have all the associated K% shown for each Unique Name? – hoooman Jan 20 '13 at 02:40
  • Peter - the pivot seems to be exactly what I'm looking for. Thanks for your help. I'll research that to figure it out. I usually can find answers to questions without asking one, but I think I didn't quite know how to phrase the question properly to find the answer. – Anthony Tyler Jan 20 '13 at 02:41
  • Well if you want each K% field as a separate MySQL field, you're gonna need a pivot query using CASE statements or something of the like: http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by but if you're just interested in getting the data, you could check out the `GROUP_CONCAT` function in MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat – Brian Jan 20 '13 at 02:42

1 Answers1

0

If you are doing this so you can print a report, best thing to do is use a report writer that supports cross tabs. Jasper Reports does.

SQL is not really good at this kind of stuff. There are tricky ways you could get it to give you the results, but they'd be pretty silly.

Rob
  • 11,446
  • 7
  • 39
  • 57