I think you are all making this much more difficult (and slow) than necessary. For formatting purposes, you need to do two queries, but they are both very simple. I come from a Java background, so maybe I don't know the limitations of asp/php, but I'm pretty sure you are not required to have all your data pre-formated in your SQL result set!
Code:
select count(*) as cols from Table group by UserID order by 1 desc limit 1;
This gets you the number of columns across your page that you will need to create your HTML table's column headers.
Then, you just get your data:
Code:
select UserID, Account from Table order by 1,2;
Then, as you go through your result set, whenever you encounter a new UserID, you create a new row in your table.
Or, if you want to remove your business logic from your presentation logic, use the following SQL to get the number of rows you will need:
Code:
select count(distinct UserID) from Table;
This, along with the first query, will get you the dimensions to create a 2D Array that can hold all your data. You can then populate it with the second query from above, and pass it to your presentation tier.
Very simple, no repetitive DB connections for each user, and no temp table with thousands of updates.
Here is some (untested) simplistic jsp code. No error checking or closing of statements and connections, but you get the idea...
Code:
<html><header><title>All Users, All Accounts</title></header>
<body>
<table>
<tr>
<td>UserID</td>
<%
...Get your DB connection and Statement...
rs = stmt.execute("select count(*) as cols from Table group by UserID order by 1 desc limit 1");
int maxCols = -1;
if(rs.next())
{
maxCols = rs.getInt(1);
}
for(int col=1; col <= maxCols; ++col)
{
%> <td>Account <%=col%></td>
<%
}
String lastUser = "";
String thisUser = "";
rs2 = stmt.execute("select UserID, Account from Table order by 1,2");
while(rs2.next())
{
thisUser = rs2.getString(1);
if(!(thisUser.equals(lastUser)))
{
lastUser = thisUser;
// We have a new user, so close off the previous row and start a new one
%> </tr><tr>
<td><%=thisUser%></td>
<%
}
%> <td><%=rs.getInt(2)%></td>
<%
}
%> </tr>
</table>
</body></html>
Re: [JavaSavant] SQL DISTINCT Help!