Pivot report with two many-to-many relationships

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pivot report with two many-to-many relationships

Author

Topic

kule
Starting Member

3 Posts

Posted - 2007-01-05 : 06:23:42

Hi,

I've got the following schema


area
-------------
id
area_name

company_area
--------------------
area_id
company_id

company
--------
id
company_name

company_sector
---------------
sector_id
company_id

sector
------
id
sector_name

What I'm trying to acheive is a report which counts the number of companies in each sector & area e.g.


         area1  area2  area3 ...
sector1  5      1      2     ...
sector2  0      3      2     ...
sector3  9      38     1     ...
...      ...    ...    ...   ...

Because the sectors & areas change often I'm a bit stuck as to the best way to acheive this - the only thing I can think of is to do a temporary table and cursor through all the areas & sectors getting the unique pairs and doing a seperate update to calculate the count. So you end up with a table like:


area_id  sector_id  company_count
---------------------------------
1        1          5
1        2          0
1        3          9
2        1          1
2        2          3
...      ...        ...

Does anyone have any better ideas of how to acheive this?

In case it matters - its not huge amounts of data currently theres about 50 areas & sectors and about 10,000 companies (although the amount of company data is likely expand a lot)

Thanks
Luke

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 17:50:23

You don't need to use a temp table and cursor to get the counts. You can do that with a grouped joined query like this

SELECT company_area.area_id, company_sector.sector_id, count(*)
FROM company_area
INNER JOIN company_sector ON company_area.company_id = company_sector.company_id
GROUP BY company_area.area_id, company_sector.sector_id
ORDER BY company_area.area_id, company_sector.sector_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources