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?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

Stiltsoft support

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:

Tue 6-1.png

Tue 6-2.png

Seems it is exactly what you need.

All comments

Stiltsoft support

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:

Tue 6-1.png

Tue 6-2.png

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table