How to do pivot ing in ASP

Hello Experts

I have the following result set from a SQL query:

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


If you look closely you will notice that this is a ticketing system and a ticket can be assigned to more than one person. There is one row for each ticket/user assignment combination.

I need to write the results to screen in the following format:  -- Notice that there is only row for each incident and the "assigned to" names are concatanated.

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



Please help.
LVL 3
hans_larsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peh803Commented:
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
0
hans_larsonAuthor Commented:
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.
0
peh803Commented:
sure, give me a minute or three :)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

hans_larsonAuthor Commented:
Thanks. I appreciate your help. :)
0
peh803Commented:
ack...any way you should not use spaces in your database field names?  That's not really a great practice....
0
hans_larsonAuthor Commented:
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.
0
hans_larsonAuthor Commented:
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
0
peh803Commented:
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>"

%>
0
hans_larsonAuthor Commented:
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>"

%>
0
peh803Commented:
>>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?
0
peh803Commented:
Sorry, I see now that you mention "yes, it's working" :)
0
peh803Commented:
i'll see what I can come up with regarding the grouping..
0
hans_larsonAuthor Commented:
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.
0
peh803Commented:
you have "high", "meduim" and "low"

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

peh803
0
hans_larsonAuthor Commented:
yes, please.
0
peh803Commented:
Okay, try this code out:

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

sLastSev = ""
sThisSev = ""
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 REPLACE(REPLACE(REPLACE(((severity, 'high', 1), 'meduim', 2), 'low', 3), IncidentID DESC"

rs.Open sSQL, conn, 1, 3, 1
Response.write "<table cellpadding=5 cellspacing=5 border=1 width='70%'>"

bWrittenOne=false
do while not rs.EOF

  sThisSev = rs("severity")
  lThisIncidentID = rs("IncidentID")
  if (lThisIncidentID <> lLastIncidentID) then
    if bWrittenOne then
      Response.Write left(sNames, (len(sNames)-2)) & "</td></tr>"
      sNames = ""
    end if
    if sLastSev <> sThisSev then
      Response.Write "<tr><td colspan=3>"&rs("Severity")&"</td></tr>"
      Response.Write "<tr><td>Incident ID</td><td>client</td><td>assigned to</td></tr>"
    end if
    Response.Write "<tr><td>" & lThisIncidentID & "</td><td>" & rs("Client") & "</td><td>"
  end if
  bWrittenOne=true

  sNames = sNames & rs("AssignedTo") & ", "
  lLastIncidentID = rs("IncidentID")
  sLastSev = rs("severity")
 
  rs.MoveNext
loop

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peh803Commented:
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
0
hans_larsonAuthor Commented:
It shows priority in the order of High, Low, Medium instead of High, Medium, Low.
0
hans_larsonAuthor Commented:
I'll play with the SQL to order it differently.

Thank you for you help. Much appreciated!
0
peh803Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.