Combine Rows in Data Set to Comma Seperated String

I have a data set that returns results similiar to the following query using the CLASSICMODELS sample database:

select CLASSICMODELS.ORDERDETAILS.ORDERNUMBER,
CLASSICMODELS.ORDERDETAILS.PRODUCTCODE
from CLASSICMODELS.ORDERDETAILS

Here is a portion of the result of the SQL query:
ORDERNUMBER PRODUCTCODE
10100 S18_1749
10100 S18_2248
10100 S18_4409
10100 S24_3969
10101 S18_2323
10101 S18_2795

How can I create a data set that combines the values of the PRODUCTCODE column into a comma seperated string if the ORDERNUMBER is the same?

For example (using the portion of the result listed above):
ORDERNUMBER PRODUCTCODE
10100 S18_1749, S18_2248, S18_4409, S24_3969
10101 S18_2323, S18_2795

Find more posts tagged with

Comments

Hans_vd

Hi kpelzer29,

When you create a grouping on ordernumber, you can add an aggregation with the CONCATENATE function.
You put the aggregation in the header or footer row of the grouping and remove the detail rows from the table as you probably don't want them to show up anymore.

Hope this helps

Regards
Hans

kpelzer29

Hi Hans, 
 
Thank you for your help. If I understand correctly, what you are suggesting would be for when I bind the data set to a table. I actually need a way to create a data set that includes the concatenated string in one of the columns because I would like to be able to create a computed column using the concatenated string and then join the data set with another data set in order to have the columns I need to bind the joint data set with a chart. Any ideas? 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="76472" data-time="1304278714" data-date="01 May 2011 - 12:38 PM">
Hi kpelzer29, 
 
When you create a grouping on ordernumber, you can add an aggregation with the CONCATENATE function. 
You put the aggregation in the header or footer row of the grouping and remove the detail rows from the table as you probably don't want them to show up anymore. 
 
Hope this helps 
 
Regards 
Hans </blockquote>

kpelzer29

<blockquote class='ipsBlockquote' data-author="'kpelzer29'" data-cid="76474" data-time="1304281779" data-date="01 May 2011 - 01:29 PM">
Hi Hans, 
 
Thank you for your help. If I understand correctly, what you are suggesting would be for when I bind the data set to a table. Is that what you mean? I actually need a way to create a data set that includes the concatenated string in one of the columns because I would like to be able to create a computed column using the concatenated string and then join the data set with another data set in order to have the columns I need to bind the joint data set with a chart. Any ideas how I can create the data set in the format listed in my previous post? Thank you! </blockquote>

Hans_vd

Hi kpelzer29,

In order to achieve that you need to do the trick in the query.
In most databases you cannot do this in plain SQL.

One way to do this is to create a database function that takes the ordernumber and that returns the string containing the values. You then create a query like this:

SELECT ordernumber, your_function (ordernumber)
FROM orderdetails

Or if you happen to be on an Oracle 11g database, you can use the built in function LISTAGG. I'm not aware of similar built in functionality in other databases.

Regards
Hans

kpelzer29

Hi Hans, 
 
I have a better understanding of how this would work, but I am having trouble figuring out how to write the function. Do you have any suggestions? Thank you! 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="76482" data-time="1304319978" data-date="02 May 2011 - 12:06 AM">
Hi kpelzer29, 
 
In order to achieve that you need to do the trick in the query. 
In most databases you cannot do this in plain SQL. 
 
One way to do this is to create a database function that takes the ordernumber and that returns the string containing the values. You then create a query like this: 
 
SELECT ordernumber, your_function (ordernumber) 
FROM orderdetails 
 
 
Or if you happen to be on an Oracle 11g database, you can use the built in function LISTAGG. I'm not aware of similar built in functionality in other databases. 
 
 
Regards 
Hans </blockquote>

Hans_vd

Maybe, if you tell me what database you are on

kpelzer29

I am using SQL Server 2005. Any help you can give me is much appreciated! 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="76496" data-time="1304346504" data-date="02 May 2011 - 07:28 AM">
Maybe, if you tell me what database you are on </blockquote>

Hans_vd

I'm not an SQL Server expert, but it seems like you can do it in just SQL: <a class='bbc_url' href='http://www.eggheadcafe.com/software/aspnet/28523243/concatenate-string-using-group-by.aspx'>http://www.eggheadcafe.com/software/aspnet/28523243/concatenate-string-using-group-by.aspx</a> 
 
There is an example that uses an XML function and than removes the XML tags. 
I tried to do the translation to your datamodel for you, but without any guarantee: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT
ORDERNUMBER,
ProductCodes = REPLACE(
(
SELECT
PRODUCTCODE AS [data()]
FROM
CLASSICMODELS.ORDERDETAILS o2
WHERE
o2.ORDERNUMBER = o.ORDERNUMBER
ORDER BY
PRODUCTCODE
FOR XML PATH ('')
), ' ', ',')
FROM
CLASSICMODELS.ORDERDETAILS o
ORDER BY
ORDERNUMBER
</pre>
 
Regards 
Hans

kpelzer29

Thank you Hans. I tried using the code in a data set query and I am getting the following error:

org.eclipse.birt.report.data.oda.jdbc.JDBCException: Error preparing SQL statement.
SQL error #1:Syntax error: Encountered "=' at line 1, column 42.
;
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "=" at line 1, column 42.

Reason:
A BIRT exception occurred.

Hans_vd

That means there is an error in the query. As I don't have an SQL Server to test on, I can't help you debugging the query. I'm sorry.
You need an SQL Server expert here.

kpelzer29

Thank you for trying to help me - I really appreciate it. 
 
Does anyone else have any ideas? I am wondering if there is a way I can accomplish the same thing using JavaScript in BIRT instead. 
 
<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="76527" data-time="1304368424" data-date="02 May 2011 - 01:33 PM">
That means there is an error in the query. As I don't have an SQL Server to test on, I can't help you debugging the query. I'm sorry. 
You need an SQL Server expert here. </blockquote>