Table transformer - Transpose row data into column headings

[Green_ Desmond]

In a table transformer, how do I transpose data elements from a source table into columns of the same name? Possibly the column names may need to be set statically, rather than dynamically - but I prefer the latter!

The source table will look like this. Some extra columns have been removed, just to keep the example cleaner and easier.

Source table:

Date	Product	Category	Current
28/11/2023	Hybrid	Releasability	C
28/11/2023	Hybrid	Reliability	B
28/11/2023	Hybrid	Security Vulnerabilities	C
28/11/2023	Hybrid	Security Review	D
28/11/2023	Hybrid	Maintainability	A
28/11/2023	Cloud	Releasability	A
28/11/2023	Cloud	Reliability	A
28/11/2023	Cloud	Security Vulnerabilities	A
28/11/2023	Cloud	Security Review	A
28/11/2023	Cloud	Maintainability	A
14/11/2023	Hybrid	Releasability	C
14/11/2023	Hybrid	Reliability	B
14/11/2023	Hybrid	Security Vulnerabilities	C
14/11/2023	Hybrid	Security Review	D
14/11/2023	Hybrid	Maintainability	A
14/11/2023	Cloud	Releasability	A
14/11/2023	Cloud	Reliability	A
14/11/2023	Cloud	Security Vulnerabilities	A
14/11/2023	Cloud	Security Review	A
14/11/2023	Cloud	Maintainability	A

I want one of the tables to filter by 'Product' = "Hybrid", with a row per date and the columns generated from the data in the 'Category' field.

Output table for 'Hybrid'

Date	Releasability	Reliability	Security Vulnerabilities	Security Review	Maintainability
28 Nov 2023	C	B	C	D	A
14 Nov 2023	C	B	C	D	A

And I want to be able to have a different transformation to filter by 'Product' = "Cloud", but in the same structure as mentioned above.

Output table for 'Cloud'.

Date	Releasability	Reliability	Security Vulnerabilities	Security Review	Maintainability
28 Nov 2023	A	A	A	A	A
14 Nov 2023	A	A	A	A	A

I don't think the transpose function does this in the table transformer macro, so what is the clever SQL which will do this for me?

[Stiltsoft support · Accepted solution]

Hi @Green_ Desmond ,

You may filter your source table by the "Product" column with the help of the Table Filter or Table Transformer macro.

On top place the Pivot Table macro:

Seems it is exactly what you need. 

[Green_ Desmond]

Great. And can I transform the table still further to not show the ‘Literal of Category’ heading and just to show ‘Date’ as a label, rather than ‘Date/Category’. So just that one header row, looking like a normal table and not a pivot. 

Maybe this can be done with more filters or transforms or otherwise that’s why I was thinking a predominantly SQL route?

[Green_ Desmond]

Also, I just tried this and it is not what I need. The pivot is interpolating extra dates between my actual data points, 14/11/2023 and 28/11/2023.

I don't want rows for 15/11/2023, 16/11/2023, etc. Only the rows in the dataset, 14/11/2023 and 28/11/2023, etc.

[Stiltsoft support]

Hi @Green_ Desmond ,

Regarding your first question: you may wrap your Pivot Table macro in the Table Transformer macro and rename your columns as you wish.

Regarding your second question: check the Options tab of your Pivot Table macro settings. Seems that you set the Date period aggregation as "Days". Choose "None" instead and the extra calendar days will disappear.

[Green_ Desmond]

Thanks, that's promising. So, I have an excerpt, wrapped by a table transformer, then a pivot table and another transformer.

The query in the inner transformer simply filters by:

SELECT * FROM T* WHERE 'Product' = "Hybrid"

The query in the outer transformer maps column names:

SELECT T1.'Date', 
T1.'Literal of Current Maintainability' AS Maintainability, 
T1.'Literal of Current Releasability' AS Releasability,
T1.'Literal of Current Reliability' AS Reliability,
T1.'Literal of Current Security Review' AS 'Security Review',
T1.'Literal of Current Security Vulnerabilities' AS 'Security Vulnerabilities'
FROM T*
ORDER BY T1.'Date' DESC

 This gives me the correct data.

All I would hope for now is to format it nicely, same as my hand crafted version of the same, below.

Is there another macro I can place over all of this to centre align relevant columns and make the header row the colour required?

[Stiltsoft support]

You may do it in your external Table Transformer that you use to rename your columns.

This is an example of text formatting https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Changingalignment

And this a workaround how to color your header row (you can't color header rows directly because this row is always treated differently) https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-FormattingaheaderofJiraIssue 

[Green_ Desmond]

Ok, I created a header for the purposes of the merge:

I then merged the two tables. The header and the output table from the transformation of the pivot table.

There was also the existing inner transformation, simply to do a filter (by 'Product') for the pivot table:

My JQL to format the row data ended up as follows:

SELECT 
FORMATWIKI("{cell:bgColor=white}" + T1.'Date' + "{cell}") AS 'Date',
FORMATWIKI("{cell:align=center}" + T1.'Literal of Current Releasability'+ "{cell}") AS 'Releasability',
FORMATWIKI("{cell:align=center}" + T1.'Literal of Current Reliability'+ "{cell}") AS 'Reliability',
FORMATWIKI("{cell:align=center}" + T1.'Literal of Current Security Vulnerabilities'+ "{cell}") AS 'Security Vulnerabilities',
FORMATWIKI("{cell:align=center}" + T1.'Literal of Current Security Review'+ "{cell}") AS 'Security Review',
FORMATWIKI("{cell:align=center}" + T1.'Literal of Current Maintainability'+ "{cell}") AS 'Maintainability'
FROM T*
ORDER BY T1.'Date' DESC

Seems complicated, but it seems to work! 

[Stiltsoft support]

Cool, thank you for sharing!