[SQL] How to get name of column head

[huangbin chen]

Hi Experts,

I would like to achieve below table transformer:

to make a condition and get name of column head, fill the column head name into new column.

Is this possible? 

Thanks

[Stiltsoft support]

Hi @huangbin chen ,

The Table Transformer macro is provided by our app - Table Filter, Charts & Spreadsheets for Confluence.

If I understand you right, you need not to simply transpose your table but to check in which column appears the first "TRUE" and then set this column name in a separate column.

You may try the CASE WHEN function and check each column one by one. Here is an example of a similar structure: https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-UsingIf/Thenconstruct

The column names should be written in the SQL queries manually (or you can set some comments, statuses, etc.):

SELECT *,
CASE WHEN T1.'AAA' LIKE "TRUE" THEN "AAA"
WHEN T1.'BBB' LIKE "TRUE" THEN "BBB"
END
AS 'Leading'
FROM T1

[Stiltsoft support]

Also what concerns the conditional formatting (how to make cells with "TRUE" green), you may check our documentation for reference as well: https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Conditionalformatting

[Stiltsoft support]

And also your screenshot has caught my eye - as your example was created in a spreadsheet (Excel or Google Sheet), maybe it will be more convenient to switch to the Table Spreadsheet (you create your original table inside the macro) or Spreadsheet from Table macro (it turns your manually created or macro generated table into a spreadsheet automatically)?

Using cell formulas and familiar conditional formatting may be more intuitive for some cases.

[huangbin chen]

Thanks. Yes, seems this is the method to achieve what I want.

[YY Brother]

Hi @huangbin chen 

Is your question about Jira issue search result? What's your specifc business scenario then?

Regards,

YY哥

[huangbin chen]

>>about Jira issue search result?
about how to use SQL to add column head name.

Just try to find the first match and get the corresponding column head name, then fill to another new column.

[YY Brother]

Oh it’s a SQL related technical question