cross tab I think...

[jacob94]

Below is a set of data in my sql table. I need to create an asp report that displays the info sort of like a cross tab would (see below). There are multiple ids but for space I am only showing one. What is the best way to do this?

Sql data
ID Week GID Selection HID AID
1000 1 1 Red Red Blue
1000 1 2 Orng orng aqua


asp web page
ID Red @ blue Orng @ aqua
1000 Red Orng
1001 Other peoples selections.....
1002 Other peoples selections.....

 

[Sheco]

Why not loop through your ADO recordset one record at a time... each time through the loop add another <tr> to your table with as many <td> as needed... then do .MoveNext at the bottom of the loop to skip to the next record?

 

[TheCandyman]

To add to Sheco

...DB connection...
...table with header row...

do while not rs.eof
    response.write "<tr><td>" & rs("ID") & "</td>"
    response.write "<td>" & rs("Selection") & "</td></tr>"
rs.movenext
loop

 

[jacob94]

How to I make the HID and AID go across the top and the other data go down? I am so confused on this...

 

[jacob94]

ID Red @ blue Orng @ aqua
1000 Red Orng
1001 Other peoples selections.....
1002 Other peoples selections.....


The top portion is hard because it needs to be the header row and go out to the right...

 

[DotNetGnat]

i would suggest doing this at the sql query level...what kind of database are you using...

-DNG

 

[jacob94]

sql server 2000

i would love to do this in sql but not use dynamic sql because i have no idea how to code that...

have any thoughts with the data i presented above???

 

[DotNetGnat]

not using dynamic sql??...hmmm...

i was hoping to refer this link

http://www.sqlteam.com/item.asp?ItemID=2955

to you...

-DNG

 

[Norman55]

Did you manage to get a solution to this ?
i recently had the same problem trying to display stats and ending up doing lots of litte sums to get what i needed but it takes ages to load the web page now
any help would be appriciated

 

[Tarwn]

You could do this in a pair of queries, one query for the header and one for the data.

First get your header data:

SELECT DISTINCT AID + ' @ ' + HID AS HeaderName FROM YourTable ORDER BY AID + ' @ ' + HID

That gives you a recordset of just the possible column headers. You can output this in a loop to generate the header.

Now you need to get your data and it would be preferable that if theuser does not have a selection we show that as well. So for each user we will want a record that contains their ID, the header value, and whether they have a sleected value. I warn you this is a little complicated:

SELECT Users.ID, IsNull(Sels.Selection,'N/A'), Headers.HeaderName
FROM
   ((SELECT DISTINCT ID FROM YourTable) Users,
   (SELECT DISTINCT AID + ' @ ' + HID AS HeaderName FROM YourTable) Headers)
   LEFT JOIN 
   (SELECT ID, AID + ' @ ' + HID, Selection YourTable) Sels ON Users.ID = Sels.ID AND Headers.Headername = Sels.HeaderName
ORDER BY ID, HeaderName

The FROM is the magic here. We are taking a pseudo-temporary table of all of the user id's and combining every user ID with every header (table,table combines all records of the first with the second). Now that we basically have a table of user id's with every posible column header, we then left join with the original table again to pull in the actual user selections. What this will do is give us our third column of Selection for every user + column header that has a matching selection. If thereis not a match then itwill instead give us a NULL value for the Sels records. So in the original SELECT we use IsNull to convert any nulls to the string 'N/A'

This should give you a recordset that has one row with every user, the column header, and their selection. And since we ordered by ID, Headername it will group all of the users together and have the records in the same order as we output our original headers. So outputting this should be somehting like:

'assume 'rs' holds our data query results
If Not rs.EOF Then rs.MoveFirst
Dim curId
Do Until rs.EOF
   'if this is the beginning of a user, start a row and output their ID
   If curId <> rs("ID") Then
      Response.Write "<tr><td>" & rs("ID") & "</td>"
      curId = rs("ID")
   End If

   'output the selection
   Response.Write "<td>" & rs("Selection") & "</td>"

   'move to next record
   rs.MoveNext

   'if this is end of rs or changing ID's, end row
   If rs.EOF Then
      Response.Write "</tr>"
   ElseIf rs("ID") <> curId Then
      Response.Write "</tr>"
   End If
Loop
Response.Write "</table>"

In any case, I'm going to be late to work. I think the SQL above is correct, but it may have some minor syntax flaws. If anyone sees anything wron with either statement or the logic, let me know.

-T