Comma Delimited Field as Group?

[Laurelanwyn]

Hi. Using a SQL database I have a series of fields that are a comma delimited list of states. It is not a list of every state, just a list of states that qualify for a certain job category. I need to make a grouping on this state field so that I can count the number of people who do a job category per state. Does anyone have any ideas?

 

[synapsevampire]

Ever so gently and slowly run the dba through a bread slicing apparatus.

Now fix the data

I'll assume that when you say a SQL database, you mean MS SQL Server.

Since you have one row for what you mean to represent as numerous rows, we need to alter things a bit. If you had bothered to demonstrate what the output will look like people would know what you want.

One means might be to write a Stored Procedure to create a temp file which has a row for each state within the job.

A Crystal means might be to create arrays, but this is dependent upon your Crystal version and other technical aspects still unknown.

A simple means for creating a spec:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[Laurelanwyn]

I am using Crystal Version 8.5
Database is MS SQL
I do not have database access to create a stored proceedure or a temporary file.

The field look like this:
AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,ME,MD,MI,MN,MO,MS,MT,NC,ND,NE,NJ,NM,NV,NY,OH,OK,OR,PA,PR,RI,SC,SD,TN,UT,VA,VT,WA,WV,WI,WY,

There are several job categories, such as benefits advisor.
I want the report to look like this

CO AK WY UT
Benefits Advisor 7 15 13
Retirement Advisor 1 7

Something like that. Is that enough information?