Reporting on delimited fields.... best practice? suggestions?

Hi guys... I need to do some statistical reporting on the answers to registration questions. The answers are stored in pipe-delimited varchar fields to allow for one-or-many answers to a single question... here's an example:

Q: What are your fav colors?
A: [checkbox list of colors]

Now the data is stored like this:

Reg001
----
red|blue|green|yellow

Now I need to determine how many people selected each individual answer, so like, how many people picked red, how many people picked yellow... to report like this:

Red - 12
Blue - 23
Green - 15
Yellow - 9

What is the best, or suggested way, to go about splitting this data up and reporting on it? Can it be done in T-SQL, or will it need to be a combination of T-SQL and .Net language?

Thanks!

-e::The answers are stored in pipe-delimited varchar fields to allow for one-or-many answers
::to a single question...

First step: shoot the person who designed the database. If he survives, get him get a boko about sql databases for beginners.

Ok, now:

* Any normal SQL is just off. Sadly. You need to preprocess this field before accumulating the values.
* This is string manipulation. T-SQL is not particularly nice in this.

Is the number of possible answers known? I mean, the list which is stored is defined, right?

If this is the case:
* one statement can work. Accumulate (count) or whatever over a computed column comparing for a defined value.

Basically the function in the field definition can check whether the string field is like - for example - '%red%, hitting on all lines containing red. You need to run one query per color then you get the data.

Main problem: makes my toes come up - this is terribly inefficient from any SQL Lover's point of view. But I fear there simply is no other way to process this. From a relational point of view this way of storing multiple choices is a horrible aberration, and as a result SQL is not really efficient in handling it.

For using a .NET language you have (right now - before yukon) to transport all the strings to the client. Not sure this is more efficient - the bandwith requirement can simply kill it off. Processing is earsier, though - just run coutners against color values stored in a hashtable (with the current count as value).

reporting on BLOB fields

Hello,
I am hoping that someone can help me out with this.
I am trying to debug a product that I support, the application creates its
own SQL when executing reports. The report I am trying to execute requires
a
field that is a BLOB, see the SQL below, the result is not what I was
expecting... From the application the report comes up with a black for the
PSL_NOTES field but if i execute the the SQL is Query Analyzer this is the
error I get:
"Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator.
"
SQL statement:
SELECT C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES,SUM(PSDETAIL.PSD_MIN) AS PSD_MIN
FROM
PSDETAIL,PSLINES B,TCPROJ C,CHRHIS D WHERE
PSDETAIL.PSD_PSL = B.PSL_KEY AND B.PSL_PRJ *= C.PRJ_KEY AND B.PSL_CHH=
D.CHH_KEY GROUP BY
C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES ORDER BY C.PRJ_NAME ASC,D.CHH_CODE
ASC,B.PSL_NOTES ASC
Thx!
MarcSince you haven't posted DDL, it's impossible to know which column(s) might
be causing the problem. But the error message says it all. If you have
TEXT, NTEXT, or IMAGE columns in your result set, they can't be ordered,
compared, or grouped. This means that your GROUP BY is probably the issue.
One workaround, if you don't mind some data truncation (if your data is over
8000 characters) is to use the SUBSTRING function to truncate the LOB data.
So insead of:
SELECT YourLOBCol
FROM YourTable
GROUP BY YourLobCol
You could do:
SELECT SUBSTRING(YourLobCol, 1, 8000) AS YourCol
FROM YourTable
GROUP BY SUBSTRING(YourLobCol, 1, 8000)
There are some other workarounds, depending on what your actual business
requirement is. Post DDL and sample data (please, keep the LOB data small)
if you need more assistance.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Marc" <Marc@.discussions.microsoft.com> wrote in message
news:CEB1B459-2832-4766-950B-1872C53115F1@.microsoft.com...
> Hello,
> I am hoping that someone can help me out with this.
> I am trying to debug a product that I support, the application creates its
> own SQL when executing reports. The report I am trying to execute
requires a
> field that is a BLOB, see the SQL below, the result is not what I was
> expecting... From the application the report comes up with a black for
the
> PSL_NOTES field but if i execute the the SQL is Query Analyzer this is the
> error I get:
> "Server: Msg 306, Level 16, State 2, Line 1
> The text, ntext, and image data types cannot be compared or sorted, except
> when using IS NULL or LIKE operator.
> "
>
> SQL statement:
> SELECT C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES,SUM(PSDETAIL.PSD_MIN) AS PSD_MIN
> FROM
> PSDETAIL,PSLINES B,TCPROJ C,CHRHIS D WHERE
> PSDETAIL.PSD_PSL = B.PSL_KEY AND B.PSL_PRJ *= C.PRJ_KEY AND B.PSL_CHH=
> D.CHH_KEY GROUP BY
> C.PRJ_NAME,D.CHH_CODE,B.PSL_NOTES ORDER BY C.PRJ_NAME ASC,D.CHH_CODE
> ASC,B.PSL_NOTES ASC
> Thx!
> Marc|||Thanks Adam,
That will help alot!!
Marc
"Adam Machanic" wrote:

> Since you haven't posted DDL, it's impossible to know which column(s) migh
t
> be causing the problem. But the error message says it all. If you have
> TEXT, NTEXT, or IMAGE columns in your result set, they can't be ordered,
> compared, or grouped. This means that your GROUP BY is probably the issue
.
> One workaround, if you don't mind some data truncation (if your data is ov
er
> 8000 characters) is to use the SUBSTRING function to truncate the LOB data
.
> So insead of:
> SELECT YourLOBCol
> FROM YourTable
> GROUP BY YourLobCol
> You could do:
> SELECT SUBSTRING(YourLobCol, 1, 8000) AS YourCol
> FROM YourTable
> GROUP BY SUBSTRING(YourLobCol, 1, 8000)
> There are some other workarounds, depending on what your actual business
> requirement is. Post DDL and sample data (please, keep the LOB data small
)
> if you need more assistance.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Marc" <Marc@.discussions.microsoft.com> wrote in message
> news:CEB1B459-2832-4766-950B-1872C53115F1@.microsoft.com...
> requires a
> the
>
>

Reporting on blank fields in Crystal

Hi,
having a bit of trouble trying to get my Crystal report to give me a count of the blank fields in a database field...any ideas?What do you mean by 'blank'? Empty or NULL?|||use a formula

if isnull(field) then
1
else
0|||I suppose the field will be null, I will try the formula to test for this. Thanks.

Reporting off of MS CRM tying Notes entity to Opportunity entity

I am trying to create an opportunity report that includes notes from the notes entity. Unfortunatly, when I pull the fields from the notes entity into my reports, it seems there is no way to tie the notes to specific opportunities or accounts that they are associated with and I get all notes under the first item listed on my report. Is there a way to link data sets like Access lets you link tables?

Hi Lat,

MS CRM discussions occur in newsgroups rather than the forums. You can post your question there using the following link:

http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.crm&lang=en&cr=US)

-Lukasz

|||Thanks much!

Reporting off of MS CRM tying Notes entity to Opportunity entity

I am trying to create an opportunity report that includes notes from the notes entity. Unfortunatly, when I pull the fields from the notes entity into my reports, it seems there is no way to tie the notes to specific opportunities or accounts that they are associated with and I get all notes under the first item listed on my report. Is there a way to link data sets like Access lets you link tables?

Hi Lat,

MS CRM discussions occur in newsgroups rather than the forums. You can post your question there using the following link:

http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.crm&lang=en&cr=US)

-Lukasz

|||Thanks much!

Reporting Financial Ratings

I'm running into 'CLS-compliance' problems trying to use characters such as "+" and "-" as reporting fields. For example, Standard and Poors ratings have these characters to indicate a notch between main letter rating categories. I have reports that need to display distributions of these various ratings in column charts. However, the X axis labels refuse to print because of these characters!

Any suggestions? This is an awfully common type of labelling in this business!

Thanks

Try Char(43) instead of "+" and Char(45) instead of "-".