?
Solved

How to do pivot ing in ASP

Posted on 2004-11-17
20
Medium Priority
?
238 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:hans_larson
  • 11
  • 9
20 Comments
 
LVL 19

Expert Comment

by:peh803
ID: 12607431
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
 
LVL 3

Author Comment

by:hans_larson
ID: 12607575
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
 
LVL 19

Expert Comment

by:peh803
ID: 12607603
sure, give me a minute or three :)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Author Comment

by:hans_larson
ID: 12607616
Thanks. I appreciate your help. :)
0
 
LVL 19

Expert Comment

by:peh803
ID: 12607622
ack...any way you should not use spaces in your database field names?  That's not really a great practice....
0
 
LVL 3

Author Comment

by:hans_larson
ID: 12607635
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
 
LVL 3

Author Comment

by:hans_larson
ID: 12607656
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
 
LVL 19

Expert Comment

by:peh803
ID: 12607702
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
 
LVL 3

Author Comment

by:hans_larson
ID: 12608399
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
 
LVL 19

Expert Comment

by:peh803
ID: 12608429
>>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
 
LVL 19

Expert Comment

by:peh803
ID: 12608486
Sorry, I see now that you mention "yes, it's working" :)
0
 
LVL 19

Expert Comment

by:peh803
ID: 12608490
i'll see what I can come up with regarding the grouping..
0
 
LVL 3

Author Comment

by:hans_larson
ID: 12608503
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
 
LVL 19

Expert Comment

by:peh803
ID: 12608505
you have "high", "meduim" and "low"

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

peh803
0
 
LVL 3

Author Comment

by:hans_larson
ID: 12608553
yes, please.
0
 
LVL 19

Accepted Solution

by:
peh803 earned 400 total points
ID: 12608593
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
 
LVL 19

Expert Comment

by:peh803
ID: 12608609
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
 
LVL 3

Author Comment

by:hans_larson
ID: 12609106
It shows priority in the order of High, Low, Medium instead of High, Medium, Low.
0
 
LVL 3

Author Comment

by:hans_larson
ID: 12609198
I'll play with the SQL to order it differently.

Thank you for you help. Much appreciated!
0
 
LVL 19

Expert Comment

by:peh803
ID: 12609331
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question