Text field in select- aggregate-group by

[Shal2]

Hi all,

I have a select statement with a text field along with other fields with aggregate functions. If I use the text field in my group by I get the following:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator"

If I don't use it, then I get the following:

"invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

How can I get rid of this?

Thank you,
Shal

 

[nigelrivett]

Use convert(varchar(8000),txtfld) for the group by - you may still get problems with rowsize in a work table.

You can also create a derived table from the group by without the text field then join to the table to get one of the text values for each row.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.