I am trying to concatenate demographic data. Customers are allowed to enter multiple responses (up to five) for their race/ethnicity. The data is stored in its own table listing the results by line number. I would like to concatenate the responses so the data is displayed in one field (rather than multiple rows) and so each customer only has one race/ethnicity. Any suggestions?
Thanks!
mish (BOB member since 2008-02-14)
Hi Mish,
As you said, the 5 details you want to concatenate are already stored in the own table.
Create one column in report, and create a function concatenating the 5 strings.
If I am going wrong, please let me know.
vchithambarkumar (BOB member since 2008-05-22)
I find it easiest to perform the text aggregation in the data provider (command object). You will have to write the SQL to do it. This is one method using Oracle:
Select Customer_ID,
Replace(SubStr(Max(Sys_Connect_By_Path(DemoData, ‘|#$’)), 4, 4000), ‘|#$’, Chr(10)) DemoData
From (Select NPMT.Customer_ID,
Replace(Replace(Trim(DemoData.DemoData), Chr(13), Chr(10)), Chr(10) || Chr(10), Chr(10)) DemoData,
Row_Number() over (PARTITION BY DemoData.Customer_ID Order By DemoData.RowID) RowNumber
From DemoData
Order by DemoData.Customer_ID)
Where Level = RowNumber
Connect by Prior RowNumber = RowNumber - 1 and
Prior Customer_ID = Customer_ID
Group by Customer_ID
Without knowing your data structure this is just a guess.
BDeLong (BOB member since 2008-01-15)