Table filter and charts SQL Query

[Craig_Harley]

Apologies to the community in advance, as this is more around my limited SQL knowledge

Essentially, I am using the table and filter macro to perform a join on a Jira view and 2 page property reports.

I am using the following query

SELECT

T2.'URI', T2.'Description', T2.SoR, T1.Lifecycle, T1.Jira Key, T3.'Consumer'

FROM T1

JOIN T2 ON T1.URI = T2.'URI'

JOIN T3 ON T3.'URI' = T2.'URI'

In table 3 there are multiple entries per URI (1 for each consumer), I want to be able to display the joined table with a single entry for each URI with a comma separated list of all the consumers of that URI.

For example a URI could have 3 rows, each with a different consumer and i would like to display it as 1 entry for the URI with the following in the consumer field "Consumer 1, Consumer 2, Consumer 3".

I can provide simplified screenshots of my source tables and what result i get vs what result i desire

[Alexey Mikhaylov _Stiltsoft_]

Hello @Craig_Harley,

It seems that you use the app that we develop - Table Filter, Charts & Spreadsheets for Confluence - and its Table Transformer macro.

Could you please, as stated, provide simplified screenshots of your source tables and what result you get vs what result you desire? This will help to understand your case better.

Let me also take this opportunity to note you can always reach out to our support portal when having any specific questions.

[Craig_Harley]

hi Alexey, thank you for the quick response. Here are the screenshots

tables used

current and desired result

[Alexey Mikhaylov _Stiltsoft_]

Hello @Craig_Harley ,

Thank you for the provided information.

Please try this SQL query:

SELECT

T2.'URI',

T2.'Description',

T1.'Lifecycle',

T1.'Jira Key',

T2.'SoR',

CONCAT_VIEW_AGGR(FORMATWIKI(T3.'Consumer', ", ")) AS 'Consumer List'

FROM T1

JOIN T2 ON T1.URI = T2.'URI'

JOIN T3 ON T3.'URI' = T2.'URI'

GROUP BY T2.'URI', T2.'Description', T1.'Lifecycle', T1.'Jira Key', T2.'SoR'

This will produce a trailing comma in the end of the string, so you can change ", " to "\n" inside FORMATWIKI statement to have a list where each item starts from a new line.

[Craig_Harley]

Much appreciated Sergey, I am unable to try this at the moment as we are currently experiencing some performance issues with the table filter and charts macro, due to having a new 3rd party vendor look after our atlassian estate. 

I am sure i will be in touch via the normal support channels to ask for assistance for Stilltsoft to help the new tooling team we have

[Alexey Mikhaylov _Stiltsoft_]

Sure @Craig_Harley, just reach out to our support portal when having any specific questions, we will be happy to help.

[Craig_Harley]

hi Alexey, this works really well, however there is just one issue and that is the formatting.

The result i get in the table produced displays like this

Consumer 1

,

Consumer 2

,

Consumer 3

,

Ideally would like to see like this

Consumer 1,

Consumer 2,

Consumer 3

or

Consumer 1, Consumer 2, Consumer 3

any advice?

[Craig_Harley]

additionally I have tried to set specific widths for each of the column tables using the stylesheet option, but not having any success.

have even just tried using a blanket all approach

 td {width:200px; }

have i missed something?