Analytics Report - Fetching Comma Separated values as rows

[vmudagal1]

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

[rushikeshvartak]

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

[vmudagal1]

Hi @rushikeshvartak 

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

CP4 = server1;server2;server3

CP5=ApplicationName

Expected result 

cp4	cp5
server1	ApplicationName
server2	ApplicationName
server3	applicationName

Thank you, 

VIdya D Mudagal

[rushikeshvartak]

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.