Using Table Transformer SQL to group matching data into single table row

[Scott Gillespie]

I have 3 tables with sprint info including sprint start and end dates (T1), resource name list (T2) with other data fields like Role, etc., and holiday information by resource (T3). I need to find any holidays for a given resource that fall within the sprint start and end dates. Ideally, the result will be an updated T2 table listing each resource with a calculated column showing one or more holidays for that resource, if any.

Resource     Holidays This Sprint      Role

Name 1       08/14/23                       Dev

Name 2       08/14/23, 08/15/23       Dev

Name 3                                             Lead

Name 4       08/15/23                        QA

Edit:  I figured out the solution with the updated SQL below, which puts multiple dates on same row, separated by commas. This works well enough, but I still have question on the separator--can I separate using line break/carriage return?

Earlier reading indicated this is not supported, but this Stiltsoft page suggests it is possible (see Adding line breaks section):  https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html  I tried FORMATWIKI and FORMATMARKDOWN to use a line feed, but it doesn't work.  

SELECT
T1.'Engagement',
T2.'Resource',
T2.'Role',

(SELECT GROUP_CONCAT(FORMATDATE(T3.'Holiday Converted Date', '%d %b %Y'))
FROM T3 WHERE T3.'Resource' = T2.'Resource'
AND T3.'Holiday Converted Date' BETWEEN T1.'Sprint Start Date' AND T1.'Sprint End Date' )
AS 'Holidays This Sprint',

T2.'Planned Dev Days',
T2.'Nights and WEs (Days)',
T2.'Holidays (Days)',
T2.'Actual Dev Days',
T2.'Comments'
FROM T1 JOIN T2 ON T2.'Engagement' = T1.'Engagement'
ORDER BY T2.'Resource'

[Stiltsoft support · Accepted solution]

Hi @Scott Gillespie ,

Please try the following SQL query:

SELECT
T1.'Engagement',
T2.'Resource',

FORMATWIKI((SELECT SUM(T3.'Holiday Converted Date' + " \n")
FROM T3 WHERE T3.'Resource' = T2.'Resource'
AND T3.'Holiday Converted Date' BETWEEN T1.'Sprint Start Date' AND T1.'Sprint End Date'))
AS 'Holidays This Sprint'

FROM T1 JOIN T2 ON T2.'Engagement' = T1.'Engagement'
ORDER BY T2.'Resource'

Hope it helps your case.

[Scott Gillespie]

Thank you very much!  I was hitting all around this but had been omitting the SUM function.

[Stiltsoft support]

Glad we could help!

The trick was even not in the SUM function but to put the FORMATWIKI before the subquery with the internal SELECT.

Like not SELECT SUM(FORMATWIKI...) or SELECT GROUP_CONCAT(FORMATWIKI...) but FORMATWIKI(SELECT ...). Maybe that's why it wasn't working for you - it wasn't a plain command but a subquery string.