Crosstab Dependency

[cable]

I've got a crosstab query that other queries depend on, unfortunatly I run into problems if the crosstab query returns less columns than the other queries are expecting!

Crosstab is:

TRANSFORM Count(EmpLevel.EmpNo) AS CountOfEmpNo
SELECT EmpLevel.EmpNo
FROM EmpLevel
GROUP BY EmpLevel.EmpNo
PIVOT EmpLevel.Level;

Which returns something like
emp no 0 1 2 3 4 5 as the headings, and then each employee and a 1 under their level.

One of the queries that depends on this is:

SELECT Sum(EmpLevel_Crosstab.[0]) AS Exec, Sum(EmpLevel_Crosstab.[1]) AS [Level 1], Sum(EmpLevel_Crosstab.[2]) AS [Level 2], Sum(EmpLevel_Crosstab.[3]) AS [Level 3], Sum(EmpLevel_Crosstab.[4]) AS [Level 4], Sum(EmpLevel_Crosstab.[5]) AS [Level 5], Sum(EmpLevel_Crosstab.[6]) AS [Level 6], Sum(EmpLevel_Crosstab.[7]) AS [Level 7]
FROM EmpLevel_Crosstab;

Is there a nice way to get this query to work whatever the number of columns? or am i hoping for far too much

 

[cable]

Fixed it!

You can insert the crosstab results into a table with as many fields as you like:

INSERT INTO CrossTab SELECT EmpLevel_Crosstab.* FROM EmpLevel_Crosstab;

As you can see the insert doesn't care how many fields the crosstab returns.

The dependent queries can the reference the table that has a known maximum, and can be made to cope if a field is null.

 

[cable]

does this work even if it only returns "Mon","Tue","Wed"?

 

[cable]

ah right, so I think I could have used that method then.
my method works as well (even if its more steps) and as an advantage in that its just SQL and I can set the empty colums to something other than null.