Group By with Text field question
Posted on 2006-05-08
I have a table named Case_Text with the following layout
(Note: This is a vendor table so changing the table layout is not an option)
ColumnName Type Length
company_id CHAR 3
case_id INT 10
case_text_seq INT 10
the following are some sample records:
company_id case_id case_text_seq case_text
BSG 1 1 This is case issue 1
BSG 1 2 This is case issue 2
BSG 2 1 This is case 2 issue 1
I am tying to write a group by sql statement that will group by company_id and case_id.
my objecitve is to get a result that looks like the following:
company_id case_id Case_Text
BSG 1 This is case issue 1
This is case issue 2
BSG 2 This is case 2 issue 1
I'm getting the dreaded "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I guess I could try to cast the text field to nvarchar but the text field can have way over 4000 characters which is what I think the limit of nvarchar is.
I was thinking of maybe using a derived table but not sure how to do that
Any help/ideas would be greatly appreciated