Merging columns to count instructors

[Bruce Fratto]

I'm trying to count then number of instructors for a course. Here's the table structure:

Trainer 1     Trainer 2

Bob             Sonya

Randy          Tom

Sonya           Randy

There are 2 trainers for each course. They could be in either column. I've merged the cells using SQL but now they are all in 1 cell and I can't count them.

Desired output

Bob - 1

Randy - 2

Sonya - 2

Tom - 1

[Stiltsoft support · Accepted solution]

Hi @Bruce Fratto ,

As you've mentioned SQL queries, I assume you use our Table Filter and Charts for Confluence app and its Table Transformer macro.

If this is the case, you may use the following structure:

Here I copied my test table twice for simplicity but you may reuse it via the Table Excerpt/Table Excerpt Include macros. In this case, you'll have only one source table, all the updates will be passed to its copy automatically.

In the internal Table Transformer macros we aggregate our data:

SELECT "Count 1" AS 'Name',
'Trainer 1' FROM T1
PIVOT (COUNT('Trainer 1') for 'Trainer 1')

Then we go to the Settings tab and check the "Transpose result table" option.

The same goes for the second internal Table Transformer macro:

SELECT "Count 2" AS 'Name',
'Trainer 2' FROM T1
PIVOT (COUNT('Trainer 2') for 'Trainer 2')

The "Transpose result table" option is also checked.

Then we move to the middle Table Transformer macro and use the following SQL query:

SELECT 'Name',
CASE WHEN 'Count 1' IS NULL
THEN "0"
ELSE 'Count 1'
END
AS 'Count 1',
CASE WHEN 'Count 2' IS NULL
THEN "0"
ELSE 'Count 2'
END
AS 'Count 2'
FROM
(SELECT * FROM T1 OUTER JOIN T2 ON T1.'Name' = T2.'Name')

Now we go to the external Table Transformer macro and perform the result calculations:

SELECT *,
'Count 1' + 'Count 2' AS 'Total Count'
FROM T1

Hope it helps your case.

[Bruce Fratto]

Your tag "Rising Star" is misleading... Should read "Rock Star". There are many lessons learned here.

- You know what you are doing

- I should have asked for help hours ago

- This answer is way above my pay grade

- I am obviously higly impressed and appreciative of your quick response and it works like a charm!

Thank you!