Concatenate column values as string in calculated field

[Heineperson]

Hello,

I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string.

For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. 

SELECT STRING_AGG(tblBG.Garden,",")

FROM tblBG

WHERE tblBG.PlantID = [@field:PlantID];

I get an error that say there is a syntax error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?
 

[casyana]

This discussion might be helpful 

 

 

[Mathilda]

On 9/12/2017 at 7:55 PM, Heineperson said:

Hello,

I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string.

For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. 

SELECT STRING_AGG(tblBG.Garden,",")

FROM tblBG

WHERE tblBG.PlantID = [@field:PlantID];

I get an error that say there is a syntax error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?
 

Calculated field can return only one value. Perhaps, you may create a view, join tables and then use that view as a data source for report with grouping.

Hope that helps.

[heinepeople]

This code always helps me! 

SELECT CAST( CommonName AS nvarchar) + N' | ' FROM  tblPlantCommonName  WHERE CPCNum = [@field:CPCNumer] for xml path(N'')

 

[DesiLogi]

On 3/29/2019 at 1:24 PM, heinepeople said:

This code always helps me! 

SELECT CAST( CommonName AS nvarchar) + N' | ' FROM  tblPlantCommonName  WHERE CPCNum = [@field:CPCNumer] for xml path(N'')

 

This is EXTREMELY helpful- thanks for posting!

[Kurumi]

Hi - Just an addition to this, if you would like to display multiple values (comma and quotation marks separated), you can use this formula:

STUFF((SELECT ', ' +'"'+ (FIELDNAME) +'"' FROM TABLE_NAME FOR XML PATH ('')), 1, 1, '')

Result:
"test1.com", "test2.com", "test3.com", "test4.com"