Pivot and categories LIKE Usage

Question

My field has many distinct names such as:

Partner
Partner UK
Partner USA
Associate - Senior
Associate - Junior
Junior Associate

And so on (more in the data than i've included here).

I was originally pivoting my data as follows:

...pivot (count([NAME] for employee_rank in ([Partner], [Associate], [Analyst]) as pivottable

Until I realised I was missing all the sub-variants of each level.

Is it possible to make the pivot categories more flexible such as:

...pivot (count([NAME] for employee_rank in ([LIKE '%Partner%'], [LIKE '%Associate%'], [LIKE '%Analyst%']) as pivottable

Answer 1

No, you can't do that, with PIVOT. I would recommend looking at a Cross Tab, which is far more flexible.

This is Pseudo-SQL, but it would look something like this:

SELECT {GROUP BY Columns},
       COUNT(CASE WHEN employee_rank LIKE '%Partner%' THEN [Name] END) AS Partners,
       COUNT(CASE WHEN employee_rank LIKE '%Associate%' THEN [Name] END) AS Associates,
       ...
FROM YourTable
WHERE ...
GROUP BY {GROUP BY Columns};