Toad World® Forums

Cross Tab Query


#1

I have a small query with millions a records. I want to create a cross tab query like in access, but am unsure of the SQL. I thought that TDP did it. I cannot use the grid pivot option because I will connect the to the view of the cross tab. Here is the SQL:

SELECT GP_CHRGS.SITE_ID,

GP_CHRGS.FACILITY_ID,

GP_CHRGS.SVC_CODE,

SUM (GP_CHRGS.QTY) AS QTY,

SUM (GP_CHRGS.CHRGS) AS CHRGS,

GP_CHRGS.PAYOR_NO,

GP_CHRGS.PAYOR_GROUP_NO,

GP_CHRGS.INSURANCE_CODE_1,

GP_CHRGS.INSURANCE_NAME_1,

GP_CHRGS.PERIOD

FROM DSS.GP_CHRGS GP_CHRGS

GROUP BY GP_CHRGS.SITE_ID,

GP_CHRGS.FACILITY_ID,

GP_CHRGS.SVC_CODE,

GP_CHRGS.PAYOR_NO,

GP_CHRGS.PAYOR_GROUP_NO,

GP_CHRGS.INSURANCE_CODE_1,

GP_CHRGS.INSURANCE_NAME_1,

GP_CHRGS.PERIOD

I would like to have the period go across as the columns and the rest as a row. Any ideas?

Thanks,

Paul T. Smith


#2

Oracle PIVOT function maybe ???

http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html