Query??

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jus SSCrazy Points: 2534 More actions · Answer 1

Create a temp table with columns:

Category , Apple, banana , peas

Insert distinct categories in to the temp table.

then loop thru the original table to update temp table inside the loop.

eg:

If Diagnosis='Apple'

update temptable set Apple='Y' where category = @categoty

If Diagnosis='banana'

update temptable set banana='Y' where category = @categoty

If Diagnosis='peas'

update temptable set peas='Y' where category = @categoty

Select * from temptable

Denby Hall of Fame Points: 3120 More actions · Answer 2

don't understand........

what is the @category for?

any other suggestions guys?

Jan-155192 SSCrazy Points: 2613 More actions · Answer 3

Would this be what you mean?

SELECTCASE WHEN(COUNT(CASE WHEN Diagnosis = 'Apple' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [Apple],

CASE WHEN(COUNT(CASE WHEN Diagnosis = 'Banana' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [Banana],

CASE WHEN(COUNT(CASE WHEN Diagnosis = 'Peas' THEN 1 END) = 1) THEN 'yes' ELSE 'no' END AS [peas]

FROM test2

GROUP by Category

Jan

Denby Hall of Fame Points: 3120 More actions · Answer 4

Thanks Jan ur a genious...... worked like a charm