How to pull info out of a specific field? 1

[mGis]

The Dbase is running on SQL Server 7.0.
I have a field that is broken into subset's of data seperated by comma's.
table looks like:
tblAnswers

Ans_QuestionnaireID        = Relates to Product
Ans_Question               = Relates to Question Number
Ans_String_Val(varChar1024)= Is the answer to the Question
Ans_UserID                 = User ID

This is a Data Field that is filled(with Radio Boxes) by users on a web site.

Ans_QuestionnaireID=345 relates to pet's.
Ans_Question=6 ask what types of pets they own.
If the Choices are Cat, Dog, Bird, Other, None
The Data Field will look like :
If they own only a

      Cat: Ans_String_Val = 'on,,,,'
      Dog: Ans_String_Val = ',on,,,'
     Bird: Ans_String_Val = ',,on,,'
    Other: Ans_String_Val = ',,,on,'
     None: Ans_String_Val = ',,,,on'
If they own a Cat and a Dog 
Cat & Dog: Ans_String_Val = 'on,on,,,'   Ect., Ect.

1.Is there a way to run a query that looks like

Ans_UserID     CAT     DOG     BIRD     OTHER     NONE
    1          YES     YES     NO       NO        NO
    2          NO      NO      NO       NO        YES
    3          NO      YES     NO       YES       NO

2.How can I ask for a list of users that own Dogs
SELECT Ans_UserID FROM tblAnswers WHERE
Ans_QuestionnaireID=345 AND
Ans_Question=6 AND
Ans_String_Val=',on,,,'
BUT this is only good if they own only a dog! I need a complete list of dog owners, whether or not they own other pets.

I asked this in the ANSI_SQL Forum thread220-67351,
but know one was able to help, so I thought someone in here has come accross this before.

Thanks, Michael
Sorry for such a long post, but wanted to completely explain it.

 

[fluteplr]

Um, design the database properly in the first place?

 

[mGis]

The dBase has already been implemented into our site(not designed by myself). Can anybody help with this query, without a re-design of the entire dBase(over 40+ tables)?
The functionality of it works great for storage, but retreival(analysis) of the info is a problem!!!!
Thanks,
Michael.

 

[fluteplr]

Look I don't want to beat you up about this, but saying a database is great for storage but retrieval is a problem is a lot like saying throwing paper files into the ocean is great for storage but retrieval is a problem. If you can't get the data back in a usable format what is the good of storing it.

I do not know of a sql server function that will allow you to retrieve the data stored in that manner. If you were using SQL 2000 you could probably write a user function that would work.

You do not need to redesign the entire database just the question and answer parts, they should be child tables of the table you describe.

Another possiblity is to retrieve all the data into a client program written in something like Visual Basic or Visual Foxpro that includes the type of parsing functions you would need to read the data in its current format.

 

[Vancouverite]

Yes, but it's complex:

1.Is there a way to run a query that looks like

Ans_UserID CAT DOG BIRD OTHER NONE
1 YES YES NO NO NO
2 NO NO NO NO YES
3 NO YES NO YES NO


Select Ans_UserID,
CASE When Ans_String_Val LIKE 'on%' Then 'YES'
ELSE 'NO' END As CAT,
Case WHEN WHEN Patindex('%,on,%,%,%') >0 Then 'YES'
ELSE 'NO' END As DOG,
CASE WHEN Patindex('%,%,on,%,%') >0 Then 'YES'
ELSE 'NO' END As BIRD,
CASE WHEN Patindex('%,%,%,on,%') >0 Then 'YES'
ELSE 'NO' END As OTHER,
CASE WHEN Patindex('%,%,%,%,on') >0 Then 'YES'
ELSE 'NO' END As NONE
FROM tblAnswers
WHERE Ans_QuestionaireID = 1 And Ans_QuestionID = 1


2.How can I ask for a list of users that own Dogs
SELECT Ans_UserID FROM tblAnswers WHERE
Ans_QuestionnaireID=345 AND
Ans_Question=6 AND
Patindex('%,on,%,%,%', Ans_String_Val) > 0

 

[Andel]

Vancouverite's technique works well... i'm just modifying it to eliminate syntax error.


Select Ans_UserID,
CASE When Ans_String_Val LIKE 'on%' Then 'YES'
ELSE 'NO' END As CAT,
Case WHEN Patindex('%,on,%,%,%', Ans_String_Val) >0 Then 'YES'
ELSE 'NO' END As DOG,
CASE WHEN Patindex('%,%,on,%,%', Ans_String_Val) >0 Then 'YES'
ELSE 'NO' END As BIRD,
CASE WHEN Patindex('%,%,%,on,%', Ans_String_Val) >0 Then 'YES'
ELSE 'NO' END As OTHER,
CASE WHEN Patindex('%,%,%,%,on', Ans_String_Val) >0 Then 'YES'
ELSE 'NO' END As NONE
FROM tblAnswers
WHERE Ans_QuestionaireID = 1 And Ans_QuestionID = 1

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[mGis]

Thanks Vancouverite,
This is what I needed.

Good catch Andel, got rid of that Extra 'WHEN'in the Second Case.

FlutePlr,
You're ABSOLUTLY correct.
I understand that it is not a correct design, if you can't retrieve the information. 1/2 of the point of a dBase id to put info into it, the other 1/2 is to retrieve it. If it is difficult to retrieve, it is not a good design. I need the info right now, so I needed the quick fix, before a Re-Design.

Thanks All for you're HELP!!!!!
Michel