Needed help in PIVOT function in SQL enabled table transformer.

Question

Hi Team, 
I wanted to create Matrix with Project in Y axis and Year and X Axis and cell contains count of issues.
 I am using the Below Code:

SELECT T1.'Project',
[2015] as Year_2015,
[2016] as Year_2016,
[2017] as Year_2017,
[2018] as Year_2018,
[2019] as Year_2019,
[2020] as Year_2020,
[2021] as Year_2021,
[2022] as Year_2022,
[2023] as Year_2023
FROM ( SELECT T1.'Project',YEAR(T1.'Created') as Year_,T1.'Key') as Source_Table
PIVOT
(
COUNT(T1.'Key') FOR Year_
IN ([2015],[2016],[2017],[2018],[2019],[2020],[2021],[2022],[2023])) AS pivottable

Order by T1.Project

In above code, its says "FOR " is not supported. Could anyone help in this

Stiltsoft support · Answer

Hi,

Your initial query seems to be a bit overloaded and as I see you are trying to use the FOR function to grab the elements of the array to make a cycle or smth - I don't think that it is supported in the AlaSQL library the Table Transformer macro is based on.

If to take the table from my first screenshot into consideration and wrap it in the Table Transformer instead of the Pivot Table macro, you may try the following SQL query:

SELECT * FROM
(SELECT T1.'Project', T1.'Key',
"Year_" + YEAR(T1.'Created') AS 'Year'
FROM T1)
PIVOT (COUNT('Key') for 'Year')
ORDER BY 'Project'

But we still recommend to use the Pivot Table macro with more simple UI for data aggregation.

Mathurashish Kumar, Sant · Answer

Okay , Thanks for your help.

Since I raised query regarding not availability of "FOR". It would of great help if you can rectify problem I am facing.

In below code , Table transformer throws error"Cannot read properties of undefined (reading 'Project')" as FOR is not supported. Could help me with this.
SELECT T1.'Project',
[2015] as Year_2015,

FROM ( SELECT T1.'Project',YEAR(T1.'Created') as Year_,T1.'Key' from T1) as Source_Table
PIVOT
(
COUNT(T1.'Key') FOR Year_
IN ([2015]) AS pivottable

Order by T1.Project

Stiltsoft support · Answer

Unfortunately, it is not possible.

Via the Pivot Table macro you just count the number of related issues that come through the Jira Issues macro, there is no any JQL behind the macro.

The only JQL that is used here is the JQL inside your Jira Issues macro.