Hello Helical,
I am using MongoDB via Drill on Helical Insight 4.0. I have an ID which is created by multiple creators. I would like to display all the creators for a single ID in a single row and separated by comma. For example, if ID = abq123 is created by user1, user2 and user3, I would like to show it as
| ID | Creators |
|---|---|
| abq123 | user1, user2, user3 |
Is there any DBFunction that will help me achieve this?
Thank You,
Sesa.
Hello Sesa,
You can create a view where you can achieve the above style of data using SQL. Below is the SQL for your reference:
select a.dateCreated, a.RecipientName, a.ID, a.creators
from
(select `p`.`dateCreated`,
`p`.`ip`.`recipient`.`name` as RecipientName,
`p`.`ip`.`recipient`.`recipientCode` as RecipientCode,
`p`.`ip`.`title` as IPTitle,
`p`.`group` as IPGroup,
`p`.`ip`.`dateOfDisclosure` as iP7DateOfDisclosure,
`p`.`ip`.`stageOfDevelopment` as iP7stageOfDevelopment,
flatten(`p`.`ip`.`creators`) as creators,
`p`.`referenceNo`,
`p`.`submissionDate`,
`p`.`submittedBy`,
`p`.`_id`.`$oid` as `ID`
from mongo.nipmo.iP7 p
limit 100) a
Thank You
From Team Helical.