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.
| Author |
Topic |
|
snaayk
Starting Member
17 Posts |
 Posted - 2004-09-01 : 02:41:26
|
I'm looking for the best way to run a query that returns a crosstab. The problem is the columns are not based on a single column. In other words, depending on the particular column the criteria is using 4 or 5 columns for the correct count.All the data is stored in a table which currently has about 550K rows. I currently run a sproc everytime the table is upated (once a day) that builds a summary table. Like thisSELECT COALESCE(fname, id), tb1, tb2, tb3, tb4FROM employees right outer join (select count(*) as tb1, id from table source where .... group by id) a on employees.fid = a.id inner join (select count(*) as tb1, id from table source where .... group by id) b on a.id = b.id inner join.... I do this 14 timesEvery derived table provides a count of a particular set of criteria, they should all be joined by using the same idThe problem is I might not have a match in the employees table, in which case I want to use the id from any of the source tables. Originally I had inner joins from all the derived tables to the first table ('a'), but if the id did not exist in the 'a' table I lose the data from the rest of the tables. Outer joins provided even wacker results. I'm losing data at the more granular levels because not every id has a count for every generated column - i'd like nulls or 0's in this case.I'm currently considering the following:Selecting distinct id's from the source tables, and using nested subqueries. SELECT fid, (SELECT COUNT(*) FROM source WHERE id=fid and...) as result1, (SELECT COUNT(*) FROM source WHERE id=fid and...) as result2, .... (14 times).That seems rather intensive.Or perhaps correlated tablesSELECT distinct s.id, tb1, tb2, tb3FROM source s, (select count(*) from source where souce.id=s.id...) tb1, (select count(*) from source where source.id=s.id)tb2, ...Who do the gurus here think? Which option or is there another option I'm not considering? I'm hoping to get some suggestions before venturing into QA experimentation  |
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
|
|
|
|
|
|
|
Crosstab...of sorts