Put into Collection

[oesku]

Hello guys,

I am new to java and have the following output from a sql resultset:

Year: 2002 Total: 1341 Format: PDF
Year: 2002 Total: 77 Format: PALM
Year: 2002 Total: 1 Format: AUDIO
Year: 2003 Total: 6596 Format: PDF
Year: 2003 Total: 381 Format: PALM
Year: 2003 Total: 3 Format: AUDIO
Year: 2003 Total: 53 Format: MSR
Year: 2004 Total: 11380 Format: PDF
Year: 2004 Total: 315 Format: PALM
Year: 2004 Total: 78 Format: MSR
Year: 2005 Total: 13720 Format: PDF
Year: 2005 Total: 288 Format: PALM
Year: 2005 Total: 55 Format: AUDIO
Year: 2005 Total: 153 Format: MSR
Year: 2006 Total: 14136 Format: PDF
Year: 2006 Total: 403 Format: PALM
Year: 2006 Total: 148 Format: AUDIO
Year: 2006 Total: 157 Format: MSR
Year: 2007 Total: 15783 Format: PDF
Year: 2007 Total: 448 Format: PALM
Year: 2007 Total: 87 Format: AUDIO
Year: 2007 Total: 291 Format: MSR
Year: 2007 Total: 27 Format: BLACKBERRY
Year: 2008 Total: 9819 Format: PDF
Year: 2008 Total: 324 Format: PALM
Year: 2008 Total: 64 Format: AUDIO
Year: 2008 Total: 343 Format: MSR
Year: 2008 Total: 75 Format: BLACKBERRY
Year: 2008 Total: 6 Format: HTML MOBILE
Year: 2009 Total: 4662 Format: PDF
Year: 2009 Total: 171 Format: PALM
Year: 2009 Total: 46 Format: AUDIO
Year: 2009 Total: 269 Format: MSR
Year: 2009 Total: 99 Format: BLACKBERRY
Year: 2009 Total: 4 Format: MOBI
Year: 2010 Total: 663 Format: PDF
Year: 2010 Total: 23 Format: PALM
Year: 2010 Total: 36 Format: MSR
Year: 2010 Total: 15 Format: BLACKBERRY

What I would like to achieve is this kind of bag:
Year PDF PALM AUDIO MSR ... ...
2002 1341 77 1 n/a
2003 6596 381 3 53 ... ...

and so on. I was thinking about ArrayList and HashMap to solve the issue, but don't see exactly where to start from.

 

[Bong]

I have done something similar. I maintain a text file of hours worked against account numbers for employees by week. I have an analysis that among other things totals hours worked to date by employee by account. I use a hashtable of names and another of accounts (CLINs). I read in the data and compute totals in a 2-D array:

      public static void commaSplit(float totals[][], String line, Hashtable clins, Hashtable names) {
              String[] temp = new String[4];
              int arrix = 0;
              int lix = 0;
              while (line.indexOf(",")>-1) {
                    lix = line.indexOf(",");
                    temp[arrix] = line.substring(0,lix);
                    arrix++;
                    line = line.substring(lix+1);
              }
              temp[arrix]=line;
              if (names.containsKey(temp[1])& clins.containsKey(temp[2])) {
                  int ix1 = (int)Integer.parseInt(names.get(temp[1]).toString());
                  int ix2 = (int)Integer.parseInt(clins.get(temp[2]).toString());
                  totals[ix1][ix2] += Float.valueOf(temp[3]).floatValue();
              }
              return ;
       }

_________________
Bob Rashkin

 

[jaxtell]

Is there any particular reason you need to do this in Java? Its probably more efficient to do it in a SQL statement.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[oesku]

Yes, unfortunately there is an important reason why I have to do this in Java: I can't figure out the right sql statement which returns the structure as required, although giving a try to Case, If, subqueries and other things I wanted to avoid... IMHO there is no sql statement to accomplish this kind of data representation, given the data model..

 

[Diancecht]

I'm with jaxtell, looks like a SQL job. Did you try the SQL forum?

Cheers,
Dian

 

[oesku]

Yes, I think you're both right, Dian and jaxtell. I'll post it to SQL forum...Otherwise I'll come back to this one..

thanks guys
oscar