Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

[SQL] How to get name of column head

huangbin chen
Contributor
July 31, 2024

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? 

get-col-head-name.png

Thanks

2 answers

2 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 2, 2024

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 2, 2024

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

Like # people like this
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 2, 2024

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
Contributor
August 22, 2024

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

Like Stiltsoft support likes this
0 votes
YY Brother
Community Champion
July 31, 2024

Hi @huangbin chen 

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

Regards,

YY哥

huangbin chen
Contributor
August 1, 2024

>>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
Community Champion
August 1, 2024

Oh it’s a SQL related technical question

Suggest an answer

Log in or Sign up to answer