0

I have the requirement to build query using pivot function which should fetch some data in my opensearch dashboards. I have written query which include join but it is working in SQL Server but not in open search, so I have to come up with query using pivot function, the query is already very complex and giving me the following result which is correct but by using join

The result which I got (also Desired Result) -->

clusterID ntiTestCoulumnCounter ntiTestColumnEnumeration ntiTestColumnTinyInt
XXX 34 2 10



Table Created using following query

create table titanium (clusterID varchar (70), type varchar ( 20 ) , dataInteger bigint ( 20 ), timestamp BIGINT ( 15 ), fieldName varchar ( 25 ) , objectName varchar ( 25 ) );

INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731b", "Counter32",123456, 1603214379462,"ntiTestColumnCounter","IPX-Inbound-ACL.600");
INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731b", "Counter32",102030, 1603214379480,"ntiTestColumnCounter","IPX-Inbound-ACL.600");
INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731b", "Enumeration",1, 1603214379523,"ntiTestColumnEnumeration","IPX-Inbound-ACL.600");
INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731b", "Enumeration",2, 1603214379597,"ntiTestColumnEnumeration","IPX-Inbound-ACL.600");
INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731c", "tinyint",12, 1603214379679,"ntiTestColumnTinyInt","IPX-Inbound-ACL.600");
INSERT INTO titanium VALUES("eb7e2c2f-0753-4923-bb52-acf507f0731c", "tinyint",10, 1603214379810,"ntiTestColumnTinyInt","IPX-Inbound-ACL.600");

The query I have written --

select 
    b.clusterID as clusterID,
    
    a.dataInteger as ntiTestColumnCounter,
  
    b.dataInteger as ntiTestColumnEnumeration,
   
    c.dataInteger as ntiTestColumnTinyInt
from
    titanium as a join
    titanium as b on a.clusterID = b.clusterID
 join
    titanium as c on b.objectName = c.objectName
where a.fieldName = 'ntiTestColumnCounter' and
      b.fieldName = 'ntiTestColumnEnumeration' and
    c.fieldName = 'ntiTestColumnTinyInt'
    
ORDER BY a.timestamp DESC, b.timestamp DESC, c.timestamp DESC
LIMIT 1;

Objective
I have to attain the same result but by using query which uses pivot function rather than join because it is not working in Open search and my custom plugin is also not supporting it.

Pivot non working code I used

SELECT [clusterID], ntiTestColumnCounter,ntiTestColumnEnumeration,ntiTestColumnTinyInt FROM   
(SELECT fieldName, [clusterID] , dataInteger FROM titanium )Tab1  
PIVOT  
(  
MAX(dataInteger) FOR fieldName IN (ntiTestColumnCounter,ntiTestColumnEnumeration,ntiTestColumnTinyInt)) AS Tab2  
ORDER BY [Tab2].[fieldName]
Lalit Kumar Singh
  • 61
  • 6
  • @DaleK sir, Pivot i used earlier and did not get the perfect result as desired, so i went through with this particular thing but it also did not resolve the thing, I am very new to sql a week old guy, so i already spent 2 days to understand it but did not got it properly. so I am taking help here on SO, hope you understand the problem! – Lalit Kumar Singh Aug 19 '22 at 04:35
  • No need to call anyone on this site sir, we are all equal here. – Dale K Aug 19 '22 at 04:37

0 Answers0