i have to display a table onscreen as follows:
product_|_technical doc_|_functional doc_|_user guide_|_admin guide
But in my db i have a table which returns the results across multiple lines:
how do i combine the lines according to the product so that it displays a single line per product as in the first table? using a loop?
i'm lost :/
any help will be greatly appreciated!
1) Let the database do the transformation. This type of query is usually called a pivot query. It is done differently in different databases, so if you don't know how to go about it, try to google it out (or ask here on JDBC forum, but remember to specify your database including version). You'd then obtain rows from the database that would correspond to individual lines in your screen output.
2) Do the transformation in Java (this is what you asked about). I'd probably create a map of sets. The map would have product name as a key, the value would be a set of documentations available for the product. For every record you'd firstly see if that record is already in the map; if not, add it with a new, empty set. Then you'd obtain the set for given product (previous step ensured it would always exist) and add the documentation type to it. Outputting the resulting structure would then be quite straightforward.
If you want to ensure the product and documentations come in sorted order, you can use the TreeMap and TreeSet with proper Comparator to achieve this.
If the ordering of the doc_type can be changed on your display, you could also consider adding an orderby clause to the sql. Break to a new output row when product changes which is what I guess you may already be thinking, and populate the 'x' or what docs you have and empty cells for missing doctypes as you iterate through the resultset.