Last post May 03, 2017 03:20 PM by limno
May 02, 2017 05:50 PM|Rusty1968|LINK
I have been asked to create a report for data with the following structure: One main table and three child tables since we have multiple entries. When I join to the three child tables I start to get multiple rows. For example, if I have two records in
each child table then I get 8 (2x2x2) rows per main record, The customer doesn't like the way this looks. I have tried grouping the records by some the common values but I still get 8 rows. I have also done a sub-report inside a cell where each child table
gets a column but you have more than one row in that cell. Is there a better way to handle this data in SSRS or even possibly a way to get one row per main record and get all the child records in cells on the same row ?
May 03, 2017 05:39 AM|Chris Zhao|LINK
Is there a better way to handle this data in SSRS or even possibly a way to get one row per main record and get all the child records in cells on the same row ?
If you need all of the data concatenated into a single column in a single row, you could use STUFF and FOR XML commands. For details, see Rolling
up multiple rows into a single row and column for SQL Server data.
STUFF((SELECT '; ' + US.USR_NAME
FROM USRS US
WHERE US.SEC_ID = SS.SEC_ID
FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1
May 03, 2017 01:58 PM|Rusty1968|LINK
Thanks for the response and I have written a few concatenate functions to do that. The issue is this isn't a one column table there are up to 5 pieces of information in each table.
May 03, 2017 03:20 PM|limno|LINK
You can try to pivot your data from SSRS side.