aggregation in columns

From: orenl <orenlav_at_gmail.com>
Date: Thu, 5 Mar 2009 09:46:01 -0800 (PST)
Message-ID: <871a564c-7a24-4ad5-b05f-5f920a7022c5_at_l37g2000vba.googlegroups.com>

Hi,
I have a table named ReportData which reportes activity for a small but dynamic number of components
there are 2 columns: componentId and timeMeasurement

I want to defin a query which counts all the reports per each component per year

I can do the following query
select year(timeMeasurement), componentId, count(*) from ReportData
group by year(timeMeasurement), componentId

but I want to get the years as rows and the count per component as columns like

        | component1 | component2 | component3
1999 | 150                205                0
2000 | 5645              6345               77
2001 | 6168              9168               669

and so on...

is it possible, remember that the number of components is dynamic Thanks Received on Thu Mar 05 2009 - 11:46:01 CST