How do you use ‘Table Transformer’ Macro to order columns in a Pivot?

[Julia T]

I am trying to reorder a Pivot in Confluence which is from a Filter in Jira.  Pivot looks good but I'd like the column order to be defined by the Status column: 

Backlog, Analysing, Ready for Release, Closed

I've wrapped my pivot in the ‘Table Transformer’ Macro but I can't get the SQL right, please can anyone advise please?

SQL code:

SELECT T* ORDER BY COLUMN.status CASE WHEN COLUMN.status LIKE 'Backlog' THEN 1 WHEN COLUMN.status LIKE 'Analysing' THEN 2 WHEN COLUMN.status LIKE 'Ready for Release' THEN 3 WHEN COLUMN.status LIKE 'Closed' THEN 4 ELSE COLUMN.status END ASC

[Stiltsoft support · Accepted solution]

Hi @Julia T ,

You just need to list your columns in the required order, for example,

SELECT T1.'Column 3', T1.'Column 1', T1.'Column 2' FROM T1

Also pay attention that you need to type in T1. and wait for the autocomplete. It seems that your real column names will be 'Count Analysing', 'Count Backlog' and so on.

To rename your columns, you'll need the "AS" function:

SELECT
T1.'Column 3' AS 'Column 3 New Name'
FROM T1

And as your Pivot Table may not have some columns in the future (for example, when your backlog is empty), you'll need the COALESCE function not to get any errors:

SELECT
COALESCE(T1.'Column 3', "") AS 'Column 3 New Name'
FROM T1

And you may always refer to our documentation for step-by-step examples.

[Julia T]

Thank you so much, this is perfect