Search code examples
crystal-reports

How to return a comma separated string using Crystal SQL Expression

I want to display a string on each row (Details section) in my Crystal Report. The contents of this string will be retrieved with the help of a SQL Expression.

The SQL I have is follows: However if multiple rows are returned, I am not sure how to convert that into a Comma Separated String. I have an Oracle 11g database. 

(select distinct NAME from TEST 
 where SAMPLE_NUMBER = "TEST"."SAMPLE_NUMBER" 
 and X_BENCH <> '"TEST"."X_BENCH"')

The TEST Table looks like this: Test Table

My report will be filtered for all samples with a specific test (e.g. Calcium). For those samples on the report, My SQL Expression should retrieve all "Other" Tests on the sample. See output example.

Report Example

Solution

  • You can accomplish this with a wm_concat. WM_CONCAT takes a bunch of rows in a group and outputs a comma separated varchar.

    Using the substr function you can separate the first result with the last.

    Please note that I am dirty coding this (without a compiler to check my syntax) so things may not be 100% correct.

    select sample_number
      , substr(wm_concat(name),1,instr(wm_concat(name),",")-1) as NAME
      , substr(wm_concat(name),instr(wm_concat(name),","),length(wm_concat(name)-instr(wm_concat(name),",")+1) as OTHER_TEST_NAMES
from TEST 
where SAMPLE_NUMBER = "TEST"."SAMPLE_NUMBER" 
      and X_BENCH <> '"TEST"."X_BENCH"' 
      and rownum < 2
group by sample_number

    However, if it is not necessary to separate the name and the other test names, it actually is much simpler.

    select sample_number
      , wm_concat(name) as NAMES
from TEST 
where SAMPLE_NUMBER = "TEST"."SAMPLE_NUMBER" 
      and X_BENCH <> '"TEST"."X_BENCH"' 
      and rownum < 2
group by sample_number

    Also please try to organize your lines to make it easier to read.