We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Analytics Report - Fetching Comma Separated values as rows

vmudagal1
New Contributor III
New Contributor III

Hi All, 

We are trying to generate an anlytics report where customproperty4 of entitlements has the values as below example:

CP4 = server1;server2;server3;server4;server5;server6 (It can contain more than 100 values separated by semi colon)

Below is the query I currently used with total number of values as 4 - this has a drawback of limiting to certain value

select distinct SUBSTRING_INDEX(SUBSTRING_INDEX(ev.customproperty4, ';', numbers.n), ';', -1) as 'ServerName', EV.customproperty11 as 'Address', EV.customproperty5 as 'Application Service', EV.customproperty6 as 'Environment', EV.customproperty3 as 'Safe', EV.customproperty1 as 'Privileged Account Name', A.name as 'Account Name', A.customproperty18 as 'AD Domain' from (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers INNER JOIN entitlement_values EV on CHAR_LENGTH(EV.customproperty4) -CHAR_LENGTH(REPLACE(EV.customproperty4, ';', '')) >= numbers.n-1 INNER JOIN entitlement_types ET on EV.entitlementtypekey=ET.entitlementtypekey INNER JOIN endpoints E on E.endpointkey=ET.endpointkey INNER JOIN entitlements2 ET2 on ET2.entitlement_value2key = EV.entitlement_valuekey INNER JOIN entitlement_values EV2 on ET2.entitlement_value1key = EV2.entitlement_valuekey INNER JOIN account_entitlements1 AE1 on EV2.entitlement_valuekey=AE1.entitlement_valuekey INNER JOIN accounts A on AE1.accountkey=A.accountkey where E.endpointkey='13'

 

Does Analytics report support stored procedures or cross apply string_function()?

Please provide your valuable inputs

Thank you, 

Vidya D Mudagal

3 REPLIES 3

rushikeshvartak
All-Star
All-Star

use group_concat function https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-g...


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi @rushikeshvartak 

I am trying to do the oppasite solution of splitting comma values as rows.

CP4 = server1;server2;server3

CP5=ApplicationName

Expected result 

cp4cp5
server1ApplicationName
server2ApplicationName
server3applicationName

Thank you, 

VIdya D Mudagal

column1 : A,B,C
column2 : P, Q,R
cross join column 1 vs column 2
 
 

If you have two columns, column1 with values 'A', 'B', 'C' and column2 with values 'P', 'Q', 'R', and you want to perform a cross join to get all possible combinations of values from both columns, you can use the following SQL query:

 
-- Assuming you have a table named YourTable with columns column1 and column2 SELECT column1, column2 FROM YourTable1 CROSS JOIN YourTable2;

This query will produce a result set with all possible combinations of values from column1 and column2. Each row in the result set will contain a pair of values, one from column1 and one from column2.

Here's how the result set might look like:

column1 | column2 ---------+--------- A | P A | Q A | R B | P B | Q B | R C | P C | Q C | R

Adjust the table names (YourTable1 and YourTable2) based on the actual names of your tables.


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.