Cross-Tab query?

Results 1 to 2 of 2

Thread: Cross-Tab query?

  1. #1
    Sandy Gutheinz Guest

    Default Cross-Tab query?

    I have a query which returns a recordset of all the people and the amount they have contributed throughout the year. The <BR>database is MS SQL 7.0. Each person will have the identical amount of records returned. How do I query it to return <BR>in a cross tab format using SQL 7.0? I cannot use the Cross-Tab Report format in SQL 7.0 example because the Event <BR>type and the amount of event types returned are not preset and will not be known until query time.<BR> <BR>This is what I have:<BR> <BR>sql="Select Name, Amount, Event_type from Table1"<BR> <BR>Name Amount Event_type<BR>--------------------------<BR>Name1 35.00 EventA<BR>Name1 0.00 EventB<BR>Name1 105.00 EventC<BR>Name2 100.00 EventA<BR>Name2 230.00 EventB<BR>Name2 70.00 EventC<BR>Name3 3.00 EventA<BR>Name3 2.00 EventB<BR>Name3 6.00 EventC<BR>Name4 78.00 EventA<BR>Name4 23.00 EventB<BR>Name4 200.00 EventC<BR> <BR>This is what I want to return:<BR> <BR>**********<BR>&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;&nbsp;&nbsp;&nbsp; Name1 Name2 Name3 Name4<BR> -------- -------- ---------- -------<BR>EventA 35.00 100.00 3.00 78.00<BR>EventB 0.00 230.00 2.00 23.00<BR>EventC 105.00 70.00 6.00 200.00<BR> <BR>Thanks,<BR>Sandy Gutheinz

  2. #2
    buzz Guest

    Default RE: Cross-Tab query?

    Have you tried a transform statement then pivot statement?<BR>/// Like the following *** Note the Pivot Section has hardcoded all possible variables <BR><BR>TRANSFORM Sum([Total]*[PRCT]) AS [Value]<BR>SELECT tbl_Managers.PRCT, qry_GrossProfits.CREG, qry_GrossProfits.DSCA, tbl_Managers.NAMA, Sum([Value]) AS GTotal<BR>FROM tbl_Managers INNER JOIN qry_GrossProfits ON tbl_Managers.CREG = qry_GrossProfits.CREG<BR>WHERE (((tbl_Managers.CREP)&#060;&#062;28400))<BR>GROUP BY tbl_Managers.PRCT, qry_GrossProfits.CREG, qry_GrossProfits.DSCA, tbl_Managers.NAMA<BR>ORDER BY qry_GrossProfits.DSCA<BR>PIVOT qry_GrossProfits.LBDSCA In ("ASD", "Field Service", "HVM", "Instrumentation", "LVM", "PAC", "PLC", "PAC SYSTEM","SPD", "SWGR","UPS", "ROIBOT", "WWT");<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts