Hello experts!
I have a subDataset that looks like this:
| Col1 | Col2 |
| row1.1 | row2.1 |
| row1.2 | row2.2 |
| row1.3 | row2.3 |
I need to display data from this subdataset as comma separated list, like this: "row1.1, row1.2, row1.3.".
(Basically, all the data from certain column merged into one String, and values are separated by commas)
I am not very keen on merging the values on the server side.
I actually managed to do something like that, by using variables but it doesn't quite work. I am using variables, where subdataset is passing it's variable to main report variable and then there is single TextField on the main report. It works but the TextField is not stretching it's height.
(I think TextField is only stretching when the evaluation time is set to "now", but in this case I cannot use "now" as the value is passed after the subdataset is loaded)
Anybody achieved all of the above, but with TextField stretching properly?
2 Answers:
One solution to allow the text element to stretch is to place in a different band than the element that runs the subdataset. For instance if you currently use the detail band, you can create two detail bands, have the subdataset in the first band and the text that displays the returned value in the second band.
Regards,
Lucian
The database can return a delimited string of values for a specified field.
In Oracle, my solution would be to use the ListAGG() function.
WITH mydataset AS ( SELECT 'row1.1' AS Col1, 'row2.1' AS Col2 FROM dual UNION ALL SELECT 'row1.2', 'row2.2' FROM dual UNION ALL SELECT 'row1.3', 'row2.3' FROM dual ) SELECT '"'||listagg(col1,',')||'"' AS CSV_Values FROM mydataset UNION ALL SELECT '"'||listagg(col2,',')||'"' FROM mydataset;
Oracle & DB2
SELECT FieldA
, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
MySQL
SELECT FieldA
, GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
PostgreSQL
SELECT FieldA
, STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SQL Server
SQL Server ≥ 2017 & Azure SQL
SELECT FieldA
, STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;SQL Server ≤ 2016 (CTE included to encourage the DRY principle)
WITH CTE_TableName AS (
SELECT FieldA, FieldB
FROM TableName)
SELECT t0.FieldA
, STUFF((
SELECT ',' + t1.FieldB
FROM CTE_TableName t1
WHERE t1.FieldA = t0.FieldA
ORDER BY t1.FieldB
FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
FROM CTE_TableName t0
GROUP BY t0.FieldA
ORDER BY FieldA;
SQLite
Ordering requires a CTE or subquery
WITH CTE_TableName AS (
SELECT FieldA, FieldB
FROM TableName
ORDER BY FieldA, FieldB)
SELECT FieldA
, GROUP_CONCAT(FieldB, ',') AS FieldBs
FROM CTE_TableName
GROUP BY FieldA
ORDER BY FieldA;
