Needed help in PIVOT function in SQL enabled table transformer.

[Mathurashish Kumar_ Sant]

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

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Mathurashish Kumar_ Sant,

Please use the Pivot Table macro for the case.

As I understand, you have a table (manually created or coming from the Jira Issues macro – it makes no difference) that is similar to my screenshot:

 

Now go to the Pivot Table macro settings and configure the required output using user-friendly interface:

 

Hope it helps your case.

[Mathurashish Kumar_ Sant]

Hi @Katerina Rudkovskaya _Stiltsoft_ , thank you for help.
Can you help me with one more thing related to Pivot table. I wanted to make number in column Clickable and open in JIRA. 
For ex: If I click on 2 of Project B, It will open in JIRA showing 2 project details.

Thanks,

Sant

[Stiltsoft support]

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.

[Mathurashish Kumar_ Sant]

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]

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.