How can I change SQL data order from rows to columns?

Question

I have a snippet of code that writes the data alphabetically from a database ACROSS 3 columns on a web page.

Example:

a result   b result  c result
d result   e result  f result
g result   h result  i result

I need instead to display it alphabetically DOWN the columns, like this:

a result   d result  g result
b result   e result  h result
c result   f result  i result

Keeping in mind I have about 100 data results, it would display the first 1/3 in column one descending, then start a new column and continue, breaking it into 3 equal parts.

The code I have now that sorts across the rows is:

<% 
GL="<table width="+Z+"100%"+Z+"border=0 cellpadding=3 celspacing=3>"
sql="select * from guideDef order by guideDesc;"
rs.open sql,adoCon
colCount=0
do while not rs.eof
  colCount=(colCount+1) mod 3
  if colCount=1 then GL=GL+"<tr>" 
  GL=GL+"<td valign=middle id=menu1 width="+Z+"33%"+Z+">"+E 
  GL=GL+"<a href="+Z+"shop.asp?guide="+rs("guide")+"&city=Plantation"+Z+">"+E 
  GL=GL+rs("guideDesc")+"</a></td>" 
  if colCount=0 then GL=GL+"</tr>" 
  GL=GL+E
  rs.moveNext
loop
rs.close
if colCount=1 then GL=GL+"<td> </td><td> </td></tr>"+E
if colCount=2 then GL=GL+"<td> </td></tr>"+E
GL=GL+"</table>"
response.write GL
%>

Thanks in advance for any help. I don't write code, so I have tried for hours to change this without success.

Answer 1

Look at using the PIVOT AND UNPIVOT commands.

Answer 2

Maybe a better solution is to leave the SQL as is and handle this in the application code rather than as a result of the query.

Answer 3

This is also referred to as crosstab queries. You need to use a case statement with some aggregate function see here

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

Answer 4

Ignore all the answers about using crosstab, they aren't reading your question.

What I would do is get your results as a giant table and divide them into three different collections, then go though each row inserting item one from collection one, then collection two, then collection three, ect, until you have exhausted all three collections.

The other option would be to write code that would go down one column until one third of the results were used, then move on to the next column, but given the way HTML is ordered, that would be a little more difficult to write.

Answer 5

I believe this code will solve your problem:

<%
Set rs     = Server.CreateObject("ADODB.RecordSet")
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "your connection string here"

Const COLUMN_COUNT    = 3

Const adOpenStatic    = 3

sql = "SELECT guide, guideDesc FROM guideDef ORDER BY guideDesc;" 
rs.Open sql, adoCon, adOpenStatic

CellsRemain = rs.RecordCount Mod COLUMN_COUNT
RowCount    = (rs.RecordCount - CellsRemain) / COLUMN_COUNT

Response.Write "<div>Rendering " & rs.RecordCount & " records to a " & _
               COLUMN_COUNT & " x " & RowCount & " table with " & _
               CellsRemain & " stand-alone cells.</div>"

Response.Write "<table width=""100%"" border=""0"" cellpadding=""3"" celspacing=""3"">" & vbCrLf

done = 0
cell = 0
While done < rs.RecordCount
  Response.Write "<tr>"  & vbCrLf
  While cell < COLUMN_COUNT And done < rs.RecordCount
    cell      = cell + 1
    done      = done + 1
    guide     = "" & rs("guide")
    guideDesc = "" & rs("guideDesc")
    url       = "shop.asp?guide=" + Server.UrlEncode(guide) + "&city=Plantation"
    Response.Write "<td>"
    Response.Write "<a href=""" & Server.HtmlEncode(url) & """>"
    Response.Write Server.HtmlEncode(guideDesc)
    Response.Write "</td>"  & vbCrLf
    If cell < COLUMN_COUNT Then rs.Move RowCount
  Wend
  If done < rs.RecordCount Then 
    rs.Move -1 * ((COLUMN_COUNT - 1) * RowCount - 1)
    cell = 0
  Else
    While cell < COLUMN_COUNT
      Response.Write "<td>&nbsp;</td>" & vbCrLf
      cell = cell + 1
    Wend
  End If
  Response.Write "</tr>" & vbCrLf
Wend

Response.Write "</table>" & vbCrLf
%>

This renders your table the way you want it:

A  E  H
B  F  I
C  G  J
D

You can use the COLUMN_COUNT constant to control how many columns will be made. The algorithm flexibly adapts to that number.

What the code does is basically this:

1. open a static RecordSet object so we can jump around in it freely
2. calculate how many rows and columns we need to show all records
3. <tr>
4. jump down the RecordSet in RowCount steps, painting <td>s until <tr> is full
5. jump back to the record that's after the one we started with in step 4
6. </tr>
7. if there are still records left, go to step 3
8. render as many empty cells as we need to make the table well-formed
9. done.

Answer 6

You could divide the results up into 3 parts (if you know the row count). Print them as 3 separate tables within separate div elements. You could then float the div elements next to each other using CSS.

if this sounds like what you want to do, (since you say you don't write code) let me know if you need help with this.

Answer 7

something like this would be what I would do (in c#):

const int columns = 3;
string[] cells = GetCells(); // load your sql into this
string ret = "<table>";
int numRows = (cells.Length + columns - 1) / columns; // round up
for (int y = 0; y < numRows; y++)
{
    ret += "<tr>";
    for (int x = 0; x < columns; x++)
    {
        int elem = x*numRows + y;
        if (elem < cells.Length)
            ret += "<td>" + cells[elem] + "</td>";
        else
            ret += "<td>&nbsp;</td>";
    }
    ret += "</tr>";
}
ret += "</table>";

and GetCells() would look something like:

string[] GetCells()
{
    string sql = "SELECT guide, guideDesc FROM guideDef ORDER BY guideDesc";
    rs.Open(sql, adoCon, adOpenStatic);
    string[] ret = new string[rs.RecordCount]
    for (int i=0; i<rs.RecordCount; i++)
    {
        ret[i] = "<a href=...></a>";
    }
    return ret;
}