Need Help Pivoting Data

[manshack_one]

Here is how the data comes out from my query:

unitdesc	auditdate	dentalwro	atc
Bridgeport PPT	9/1/2009	100	98
Bridgeport PPT	10/1/2009	85	97
Bridgeport PPT	11/1/2009	67	96
Mineral Wells PPT	9/1/2009	99	89
Mineral Wells PPT	10/1/2009	89	88
Mineral Wells PPT	11/2/2009	78	87

I need to pivot the data though in the report so it's like this:

		dentalwro			atc
		9/1/2009	10/1/2009	11/1/2009	9/1/2009	10/1/2009	11/1/2009
Bridgeport PPT		100	85	67	98	97	96
Mineral Wells PPT		99	89	78	89	88	87

Will I have to script this or are there better options in iReport to pivot the data.  It needs to fill horizontally but do so for all the values in the first column (Bridgeport dentalwro values) then the next column (Bridgeport atc) before moving on to Mineral Wells dentalwro then Mineral Wells atc values.  Any suggestions would be greatly appreciated.

[manshack_one]

I'm really needing some help with this one.  It's driving me nuts.  Here's the properly formatted report that originally came from an Excel spreadsheet and the data as it's being collected now.  I need to query my data to look like that report.

 

[manshack_one]

it was all in the query:

select u.system, u.unitcode, u.unitdesc,
  s1.dentalwro as dental1, s1.dentalnwr as nwr1,
  s2.dentalwro as dental2, s2.dentalnwr as nwr2,
  s3.dentalwro as dental3, s3.dentalnwr as nwr3,
  s1.atc1 as atc11, s2.atc1 as atc12, s3.atc1 as atc13,
  s1.atc2 as atc21, s2.atc2 as atc22, s3.atc2 as atc23,
  s1.atc3 as atc31, s2.atc3 as atc32, s3.atc3 as atc33,
  s1.dentalcs as cs1, s2.dentalcs as cs2, s3.dentalcs as cs3,
  s1.dentalnsp as nsp1, s2.dentalnsp as nsp2, s3.dentalnsp as nsp3,
  s1.dentalnpr as npr1, s2.dentalnpr as npr2, s3.dentalnpr as npr3
from UNITS u
left outer join SLC s1
 on s1.unit = u.unitdesc
 and s1.auditdate between  $P{begindate} and last_day($P{begindate})
left outer join SLC s2
 on s2.unit = u.unitdesc
 and s2.auditdate between
 DATE_ADD($P{begindate}, interval 1 month) and last_day(date_add($P{begindate}, interval 1 month))
left outer join SLC s3
 on s3.unit = u.unitdesc
 and s3.auditdate between
 DATE_ADD($P{begindate}, interval 2 month) and last_day(date_add($P{begindate}, interval 2 month))
order by u.system, u.unitdesc;

[vnug]

Or, you could use crosstab to give you the output you are looking for.