How to do pivot ing in ASP

Select them all in the data set specified above, and only write a new row when the incident ID changes.  As long as the incident ID stays the same, it means that you're just looking at another person assigned to this incident.  Hope that makes sense to you.

You'll have to make sure you order by Incident ID so that your change detection code operates correctly.  

Pseudo code would be something like this:

''open recordset
''loop through recordset (do while not rs.eof)

''  compare last incident ID to current incidentID
''   if they're the same, just write another person's name in the "Assigned To" TD
''   if they're different, write an entire new row

'' move to the next item in the recordset
'' loop

Hope this helps!
peh803

I have tried this for the last 2-days now and I'm not getting any further. Could you please provide me with sample code?

Thanks.

sure, give me a minute or three :)

Thanks. I appreciate your help. :)

ack...any way you should not use spaces in your database field names?  That's not really a great practice....

I noticed you siad earlier to add the new person's name into another <td> - This will not be ideal since the list could grow to 20 users. If possible I would rather we concatenate a user list string and write that string into one <td>.

Thanks again.

sorry, I added the spaces for formatting in this post. Here is the data without the spaces:


Incident ID      Client      Severity      Assigned To
5      Customer1      Medium      Timmy Jones
5      Customer1      Medium      Billy Bob
5      Customer1      Medium      Butch Johnson
5      Customer1      Medium      Scotty Riley
3      Customer2      Low      Wiley Jones
3      Customer2      Low      Scotty Riley
1      Customer8      High      Joe Bloggs

No problem at all.  Here's the code that writes the user names into 1 TD, in the 4th column (obviously, I haven't tested it because I don't have your data source).  Let me know if you have any other questions about it....

<%
Dim conn
Dim sSQL
Dim rs
Dim sLastCatName
Dim lThisIncidentID
Dim lLastIncidentID
Dim lQuestionCounter
Dim bWrittenOne
Dim sNames

lThisIncidentID = ""
lLastIncidentID = ""
sNames = ""

set conn = server.CreateObject("adodb.connection")
set rs =  server.CreateObject("adodb.recordset")
conn.Open [your connection string here]
sSQL = "your_query ORDER BY IncidentID DESC"
rs.Open sSQL, conn, 1, 3, 1
Response.write "<table>"
Response.Write "<tr><td>Incident ID</td><td>client</td><td>severity</td><td>assigned to</td></tr>"
bWrittenOne=false
do while not rs.EOF
  lThisIncidentID = rs("IncidentID")
  if (lThisIncidentID <> lLastIncidentID) then
    if bWrittenOne then
      Response.Write left(sNames, (len(sNames)-2)) & "</td></tr>"
    end if
    Response.Write "<tr><td>" & lThisIncidentID & "</td><td>" & rs("Client") & "</td><td>" & rs("Severity") & "</td><td>"
  end if
  bWrittenOne=true
  sNames = sNames & rs("AssignedTo") & ", "
  lLastIncidentID = rs("Incident ID")
  rs.MoveNext
loop

Response.Write left(sNames, (len(sNames)-2)) & "</td></tr></table>"

%>

Thanks. Almost there - Can you please show me how to add a row with colspan=4 that lists the severity, so the data will be grouped by severity.

I include a SQL script to generate the incidents table and also to insert the data. Also, I past a copy of your code which is working.


CREATE TABLE [incidents] (
      [IncidentID] [int] NULL ,
      [Client] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Severity] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [AssignedTo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


INSERT INTO Incidents VALUES(5, 'Customer1', 'Medium', 'Timmy Jones')
INSERT INTO Incidents VALUES(5, 'Customer1', 'Medium', 'Billy Bob')
INSERT INTO Incidents VALUES(5, 'Customer1', 'Medium', 'Butch Johnson')
INSERT INTO Incidents VALUES(5, 'Customer1', 'Medium', 'Scotty Riley')
INSERT INTO Incidents VALUES(3, 'Customer2', 'Low', 'Wiley Jones')
INSERT INTO Incidents VALUES(3, 'Customer2', 'Low', 'Scotty Riley')
INSERT INTO Incidents VALUES(1, 'Customer8', 'High', 'Joe Bloggs')








<%
Dim conn
Dim sSQL
Dim rs
Dim sLastCatName
Dim lThisIncidentID
Dim lLastIncidentID
Dim lQuestionCounter
Dim bWrittenOne
Dim sNames


lThisIncidentID = ""
lLastIncidentID = ""
sNames = ""

set conn = server.CreateObject("adodb.connection")
set rs =  server.CreateObject("adodb.recordset")
conn.Open "Driver={SQL Server};SERVER=.;UID=sa;Pwd=;DATABASE=test"
sSQL = "SELECT * FROM Incidents ORDER BY IncidentID DESC"



rs.Open sSQL, conn, 1, 3, 1
Response.write "<table cellpadding=5 cellspacing=5 border=1 width='70%'>"
Response.Write "<tr><td>Incident ID</td><td>client</td><td>severity</td><td>assigned to</td></tr>"

bWrittenOne=false
do while not rs.EOF


  lThisIncidentID = rs("IncidentID")
  if (lThisIncidentID <> lLastIncidentID) then
    if bWrittenOne then
      Response.Write left(sNames, (len(sNames)-2)) & "</td></tr>"
      sNames = ""
    end if
    Response.Write "<tr><td>" & lThisIncidentID & "</td><td>" & rs("Client") & "</td><td>" & rs("Severity") & "</td><td>"
  end if
  bWrittenOne=true
  sNames = sNames & rs("AssignedTo") & ", "
  lLastIncidentID = rs("IncidentID")
 
  rs.MoveNext
loop

Response.Write left(sNames, (len(sNames)-2)) & "</td></tr></table>"

%>

>>Thanks. Almost there - Can you please show me how to add a row with colspan=4 that lists the severity, so the data will be grouped by severity.

sure, but first, do the names work?

Sorry, I see now that you mention "yes, it's working" :)

i'll see what I can come up with regarding the grouping..

I'm sorry. I was unclear - The names work. Thank you!!

I just had to change to minor things:
1. I added     sNames = ""
2. I change rs("Assigned To")  to rs("AssignedTo")   Just a typo.

you have "high", "meduim" and "low"

I assume that you want "high" incidents first, then "medium" followed by "low"?

peh803

yes, please.

Oops, sorry...typo.

This:
   sSQL = "SELECT * FROM Incidents ORDER BY REPLACE(REPLACE(REPLACE(((severity, 'high', 1), 'meduim', 2), 'low', 3), IncidentID DESC"

Should be this instead:
   sSQL = "SELECT * FROM Incidents ORDER BY REPLACE(REPLACE(REPLACE(severity, 'high', 1), 'meduim', 2), 'low', 3), IncidentID DESC"

I got a little trigger happy with the "(" key there... :)

Let me know how it works!
peh803

It shows priority in the order of High, Low, Medium instead of High, Medium, Low.

I'll play with the SQL to order it differently.

Thank you for you help. Much appreciated!

Glad to help!  Sorry about the ordering problem... let me know if you need more help with it.  

Other than that, glad I was able to help you out!

Regards,
peh803