Cross Tab Query

Toad Data Point
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

Assuming the values of the field period are constant you could edit the lines:

SUM (GP_CHRGS.QTY) AS QTY,

SUM (GP_CHRGS.CHRGS) AS CHRGS,

to something like:

SUM (IF(period= “X”, GP_CHRGS.QTY, 0)) AS QTY,

SUM (IF(period=“X”,GP_CHRGS.CHRGS,0)) AS CHRGS

Unfortunately you would have to do this for each value of Period and since this will create multiple lines per period you will need to use the edited SQL as a sub query.

SELECT

crossTab.SITE_ID,

crossTab.FACILITY_ID,

crossTab.SVC_CODE,

Sum(crossTab.QTY_X) as QTY_X,

sum(crossTab.CHRGS_X) as CHRGS_X,

Sum(crossTab.QTY_Y) as QTY_Y,

sum(crossTab.CHRGS_Y) as CHRGS_Y

FROM(SELECT GP_CHRGS.SITE_ID,

GP_CHRGS.FACILITY_ID,

GP_CHRGS.SVC_CODE,

SUM (IF(period= “X”, GP_CHRGS.QTY, 0)) AS QTY_X,

SUM (IF(period=“X”,GP_CHRGS.CHRGS,0)) AS CHRGS_X,

SUM (IF(period= “Y”, GP_CHRGS.QTY, 0)) AS QTY_Y,

SUM (IF(period=“Y”,GP_CHRGS.CHRGS,0)) AS CHRGS_Y

FROM DSS.GP_CHRGS GP_CHRGS

GROUP BY GP_CHRGS.SITE_ID,

GP_CHRGS.FACILITY_ID,

GP_CHRGS.SVC_CODE) crossTab

group by

crossTab.SITE_ID,

crossTab.FACILITY_ID,

crossTab.SVC_CODE

3

Unfortunately, the period changes every month. The period is created by an effective date and I separate the parts to create a 12 months, by month, 201210, 201211 - 201309. Each month the period advances, 201211-201212-201310.

4

What if you do a find/replace for the variable value? Find X - Replace 201211

Etc