Pivot Statement - SQL - Force a column when there is no data.

[Jim Dudley]

Situation:

I have two Cross Tab Queries that Pivot on Text fields and return a Count for each category. Occasionally, one of more of the categories may have no data/activity during the period/date range of the query's criteria.

What I want to do is to force a column for those categories. In as much as they are text fields, I would like a missing category to return "No Data" or N/D.

If a Category is missing it causes other subsequent queries that are expecting these columns to fail and generate errors, which cause automation code to crash.


The categories for the first query are: [Type] - "CD", "OD" or "PD".

The categories for the second query are: [Campus] - "MK", "NH", 'SY" or "KG".

The Pivot Counts the number of occurances under each category based on another Text [field] [SNum] or [WCode]

Both queries run on the same tmpAtt_DateRange table. This table is created based on a user input for [StartDate] & [EndDate]. (Specific Period for Comparison)


SQL:

Q1;

TRANSFORM Count(tmp_Range_WS.Workshop) AS CountOfWorkshop
SELECT tmp_Range_WS.Wcode, tmp_Range_WS.Type, Count(tmp_Range_WS.Workshop) AS [Total Of Workshop]
FROM tmp_Range_WS
GROUP BY tmp_Range_WS.Wcode, tmp_Range_WS.Type
PIVOT tmp_Range_WS.Campus;


Q2;

TRANSFORM Count(TmpAtt_DateRange.WCode) AS CountOfWCode
SELECT TmpAtt_DateRange.SNUM, Count(TmpAtt_DateRange.Type) AS CountOfType
FROM TmpAtt_DateRange
GROUP BY TmpAtt_DateRange.SNUM
PIVOT TmpAtt_DateRange.Type;


Any suggestions in the form of Pivot statements would be appreciated. I have tried using "Switch", "Iif", "IifNull" etc. and cannot get the desired results. Switch does work in another case but the field is numeric. The same logic is not working with the text fields.

Alternatively, I am open to any other suggestions to achieve my goal.


Thank you in advance for looking at this situation.


Jim

 

[jzwp22]

You will need to create a query that joins your counting query to the table that holds the categories. That join type will have to be a left join (all records of the category table and those of the counting query that match).

I'm not very good with cross-tab queries, but I think you should be able to use the new query as the basis for the cross-tab.

With respect to returning, a literal such as "N/D", you will need nested functions to return the text where a count does not exist for a particular category


I believe that you should be able to do this in the cross tab similar to this

TRANSFORM IIF(IsNull(Count(tmp_Range_WS.Workshop)), "N/D",Count(tmp_Range_WS.Workshop))
AS CountOfWorkshop

 

[Jim Dudley]

Thank you for your input. I will try your suggestion and report back on my results.

Jim

 

[sxschech]

If you want to be sure that a col is displayed regardless of having data and you know the names of the fields, you can put that in the sql statement after the PIVOT:

PIVOT tmp_Range_WS.Campus In ("MK", "NH", 'SY", "KG");

 

[Jim Dudley]

Thank you that works.

Jim