You could do this in a pair of queries, one query for the header and one for the data.
First get your header data:
Code:
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:
Code:
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:
Code:
'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
