0

See title. Basically, the data in this report is set up such that each value in Field A has multiple corresponding values in Field B, and I need to display Field B as a comma-separated list. According to the internets, this is totally easy via a combination of Join() and LookupSet() in 2008... but I'm on 2005. Anyone know how I can do this?

skaffman
  • 398,947
  • 96
  • 818
  • 769
extarbags
  • 247
  • 4
  • 12
  • This can be done but we need more information about the dataset structure. Tables, Fields, and the relationship between them. – Kenneth Jun 11 '10 at 17:22
  • It's fairly simple; there are three fields relevant to this issue. The part that I need to organize into a comma-separated list is a list of counties, and that's the third field. The first two are essentially state and region-within-state. So a sample might look like this: State...Region.....County ST......region 1...county 1 ST......region 1...county 2 ST......region 1...county 3 ST......region 2...county 4 ST......region 2...county 5 And so forth. So what I need is this: State...Region.....County ST......region1....county1, county2, county3 Anything I missed? – extarbags Jun 11 '10 at 17:36
  • Ugh, sorry about the no line breaks. What. A. Noob. – extarbags Jun 11 '10 at 17:40

1 Answers1

1

Here is my structure:

CREATE TABLE [dbo].[Regional](
    [State] [char](20) NULL,
    [Region] [char](10) NULL,
    [County] [char](20) NULL
)

Here is my query:

SELECT state,
       region,
       (SELECT Rtrim(county) + ','
        FROM   regional b
        WHERE  a.state = b.state
           AND a.region = b.region
        FOR XML PATH('')) counties,
       Count(*) countycount
FROM   regional a
GROUP  BY state,
          region

Here is the output:

state   region  counties                   countycount
AL      South   Mobile,Baldwin,           2
MS      South   Jackson,Harrison,Stone,   3

You will notice a trailing ',' that you will need to trim. That should be simple if your displaying this in SSRS.

Kenneth
  • 1,364
  • 1
  • 8
  • 11