Hello, I am trying to create an ESQL query that groups the results by the Test Case ID. For example, if I have the following table: Test Case ID Requirement 1 RQ1 1 RQ2 2 RQ3 2 RQ4 I want an ESQL query the groups the Requirements by Test Case ID and outputs: Test Case ID Requirement 1 RQ1, RQ2 2 RQ3, RQ4 For SQL, it appears that the query would be: SELECT TEST_CASE_ID, STRING_AGG(REQUIREMENT_NAME,,) AS REQUIREMENTS FROM SpiraTestEntities.R_RequirementTestCases GROUP BY TEST_CASE_ID However, ESQL removed the STRING_AGG function. Is there another way to do this? en-US(C) Copyright 2006-2026 Inflectra Corporation.support@inflectra.com/Computers/Software/Project_Management//Computers/Software/Quality_Assurance/KronoDesksupport@inflectra.comhttp://www.inflectra.com/kronodesk/forums/threads120/Support/Forum/spirateam/reports/2615.aspxthreadId=2615Jacob Carbajal (jacob.d.carbajal@lmco.com) Hello, I am trying to create an ESQL query that groups the results by the Test Case ID. For example, if I have the following table: Test Case ID Requirement 1 RQ1 1 RQ2 2 RQ3 2 RQ4 I want an ESQL query the groups the Requirements by Test Case ID and outputs: Test Case ID Requirement 1 RQ1, RQ2 2 RQ3, RQ4 For SQL, it appears that the query would be: SELECT TEST_CASE_ID, STRING_AGG(REQUIREMENT_NAME,,) AS REQUIREMENTS FROM SpiraTestEntities.R_RequirementTestCases GROUP BY TEST_CASE_ID However, ESQL removed the STRING_AGG function. Is there another way to do this? Mon, 23 May 2022 15:47:57 -04002024-08-20T00:07:18-04:00/Support/Forum/spirateam/reports/2615.aspxmessageId=5661David J (adam.sandman+support@inflectra.com) I dont think ESQL supports that Im afraid. You may want to simply group by requirement name and then use XSLT to do the further aggregating and grouping. Tue, 24 May 2022 15:28:40 -04002022-05-24T15:28:40-04:00/Support/Forum/spirateam/reports/2615.aspx#reply5661messageId=6604Erik Swedlund (erik.swedlund@ascom.com) I am looking for a solution for exactly this. @jacob, did you get a resolution to this issue? Thu, 08 Feb 2024 12:31:49 -05002024-02-08T12:31:49-05:00/Support/Forum/spirateam/reports/2615.aspx#reply6604messageId=6610Erik Swedlund (erik.swedlund@ascom.com) Fixed it with XSLT 1.0 as suggested. For anyone else getting into this issue, see my code below. XSLT ( Group Requirements by Test Case ID): # NAME REQUIREMENT STATUS VERSION BUG TC: TR: And heres my ESQL query to gather the info required for an TRF report (TC->REQ->BUG tracabillity) SELECT TC.TEST_CASE_ID AS ID, TR3.TEST_RUN_ID AS TR, TC.NAME, RQ.REQUIREMENT_NAME AS REQUIREMENT, TR3.EXECUTION_STATUS_NAME AS STATUS, TR3.RELEASE_ID, TR3.RELEASE_VERSION_NUMBER AS VERSION, I.NAME AS BUG, I.CUST_01 AS JIRA_LINK FROM SpiraTestEntities.R_TestCases AS TC JOIN (SELECT TR2.TEST_CASE_ID, MAX(TR2.TEST_RUN_ID) AS TEST_RUN_ID, MAX(TR2.RELEASE_ID) AS RELEASE_ID FROM SpiraTestEntities.R_TestRuns AS TR2 WHERE TR2.END_DATE IS NOT NULL GROUP BY TR2.TEST_CASE_ID ) AS TR ON TC.TEST_CASE_ID=TR.TEST_CASE_ID JOIN SpiraTestEntities.R_TestRuns AS TR3 ON TR3.TEST_RUN_ID=TR.TEST_RUN_ID LEFT JOIN SpiraTestEntities.R_RequirementTestCases AS RQ ON TC.TEST_CASE_ID = RQ.TEST_CASE_ID LEFT JOIN SpiraTestEntities.R_TestRunIncidents AS TRI ON TR.TEST_RUN_ID=TRI.TEST_RUN_ID LEFT JOIN SpiraTestEntities.R_Incidents AS I ON TRI.INCIDENT_ID=I.INCIDENT_ID WHERE TC.PROJECT_ID = ${ProjectId} AND TR.RELEASE_ID IN {${ReleaseAndChildIds}} Wed, 14 Feb 2024 09:02:14 -05002024-02-14T09:02:14-05:00/Support/Forum/spirateam/reports/2615.aspx#reply6610