Pivot Table, Crosstab.. ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Pivot Table, Crosstab.. ?

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2004-12-17 : 14:53:40

I have a table

Publisher | AppName | Version | Instancecount | Insertdate

I want to be able to return it like

Publisher | AppName | Version | Date | Date | Date
----------+---------+---------+------+------+-----
Here I want the instance count under the date, and I want the date to be the insertdate and if that date has no instance count then it should be 0.

There are several different dates and the instance count it already summed up in the table, so there will only be 1 record for each publisher, appname, version combo, with that date.

I have seen several different scripts that deal with the pivot table, they all seemed to work within different degrees of each other but none of them were able to handle the publisher, appname, version combo that i need.

I am sure I have forgotten some detail that would put this all together for me, so if you can help I would appriciate it.

Thanks,
J.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-17 : 14:57:40

These should help:

http://www.sqlteam.com/searchresults.asp?SearchTerms=cross+tab

Also look up the CASE expression in Books Online, there should be an example that generates a cross tab (that's how the articles here do it)

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2004-12-17 : 15:10:38

I have been looking at the case way of doing due to its speed, the only problem is that i dont know what the dates are, they are going to be dynamic and i cant use a date range in order to assign it to a month i need the exact date of the data. using the case statement approach though i would need to assign the column headers a name, i want that name to be the date that it just captured.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-17 : 16:22:29

One of the links in that list has a dynamic cross tab procedure that you can use for that kind of query.

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2004-12-21 : 15:00:50

Thanks, I was able to take one of the examples and twist it into what I was looking for.

I appriciate it.

J.

fishern
Starting Member

4 Posts

Posted - 2004-12-30 : 08:06:11

Thank you for this great piece of code it has helped tremendously.

It might be cheeky, but I would like to bet able to show the outcome of the query / stored procedure in an asp grid using vbscript

This will just allow me to finish off the exercise, any help you could offer would be very much appreciated.

Thanks in advance

Nigell

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-30 : 09:06:26

Do you know how to write ASP pages, or how to use VBScript, or how to write HTML?

If not, then you are in the wrong place.

If so, then your specific question is ... ?

- Jeff

fishern
Starting Member

4 Posts

Posted - 2004-12-30 : 12:25:00

Ok Jeff

Its not very clear what the recordset values might be for each of the colums. (Maybe Im Missing Something).

I know asp and html etc etc.

I guess I cant readily see how to get from the "crosstab" result to the final presentation in an asp table.

Sorry if this seems elementary but I could really do with your help.

Nigel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-30 : 12:29:06

the fields property of a recordset object tells you what the fields are in the recordset.


dim f   ' this will be an ADO.Field object, but in VBScript we can't declare that

-- you can use For-Each
for each f in CurrRS.Fields
   response.write f.Name & "<BR>"
next

-- or you can use a Loop:
dim i
for i = 0 to CurrRS.fields.count
   response.write CurrRs.fields(i).name  & "<BR>"
next

Does that help? You will need to dynamically build your table to have as many columns as you need using a loop like that.

- Jeff

fishern
Starting Member

4 Posts

Posted - 2004-12-31 : 05:53:51

Thanks for that Jeff. I have having problems with the recordset.

Set oConn = fncConnect()
Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = oConn
Cmd.CommandText = "EXECUTE crosstab 'select Participant_Name from Bet_Option_Participants inner join Bet_Option_Odds on (Bet_Option_Odds.Participant_ID=Bet_Option_Participants.Participant_ID)Where Bet_Option_Odds.Outcome_ID = 1013 group by Participant_Name', 'Max(Odds_Decimal)','Bookmaker_ID','Bookmaker'"

Set oRs = Server.CreateObject("ADODB.RecordSet")
Set oRs = Cmd.Execute

dim i
for i = 0 to oRs.fields.count

response.write oRs.fields(i).name & "<BR>"
next

I am getting an error
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

I will try and work it out

Thanks for your help

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-31 : 07:12:08

dim i
for i = 0 to oRs.fields.count -1

response.write oRs.fields(i).name & "<BR>"
next

should your code not be as above....ie are you overshooting the number of fields in the recordset??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources