Visual Studio: Returning multiple values in a list

Question

I am using a list in my report and one of the fields may potentially have more than one value per group, but I am unable to return multiple values. To be more specific, each page (list group) contains info for each case number on a docket. One of the fields is a citation number. Some cases may have multiple citations per case number, but only one is being returned for each case number/list group.

I have tried a list within a list approach and also just allowing it to remain a field in the list and neither have worked.

Everything I have read refers to parameters and I am not using parameters at this moment.

Any ideas on how I can achieve this? Or do I have to use coding for this? Thank you!

Since there is a lot of confidential information I would rather not display the output, but as an example:

Page 1: Case Number: 2016 XX 0000 Citation Number: XX1234 (has other citation numbers that are not displaying)

Page 2: Case Number: 2014 AA 1111 Citation Number: AB4567

An example of what I'm trying to accomplish:

Page 1: Case Number: 2016 XX 0000 Citation Number: XX1234, YY5798, ZZ3748

Hopefully this helps explain a little better.

I think it's easier to try to get the info comma-sepparated directly from the query. Are you using SqlServer as database? Oracle? In Oracle you can accomplish this using ListAgg() — vercelli, Jun 08 '16 at 21:34

score 0 · Answer 1 · edited May 23 '17 at 12:31

I followed this post ListAGG in SQLSERVER to achieve the result. I am using with yourtable to create the data, you won't need that

Data:

caseNumber  citation
2016    1234
2016    5678
2016    9ABC
2014    1234


with yourtable as (
select '2016' as caseNumber, '1234' as citation union all
select '2016' as caseNumber, '5678' as citation union all
select '2016' as caseNumber, '9ABC' as citation union all
select '2014' as caseNumber, '1234' as citation )
select distinct t1.caseNumber,
  STUFF((SELECT ', ' + t2.citation 
         from yourtable t2
         where t1.caseNumber = t2.caseNumber
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') data
from yourtable t1;

OUTPUT

caseNumber  data
2014        1234
2016        1234, 5678, 9ABC

Visual Studio: Returning multiple values in a list

1 Answers1