Group By for columns that are data type text

[Craig]

Hi there

I have a SQL query similar to the following;

select appnum, sum(assetcost), notes from contracts
group by appnum, notes

The notes column is of data type text. This statement fails because columns
with a data type of text cannot be part of a group by statement. The
application has been in production for a year so I cannot change the notes
field to a varchar() data type (nor do I want to, because the client treats
unlimited notes space as a big plus over their previous application). How
can I get a Group By statement to work when one of the columns is data type
text?

Thanks.

[Craig]

I should do more research before I post questions. I've just found that I
can use the cast function to convert the text column to varchar.

"Craig" <cjewiss.nospam@hotmail.com> wrote in message
news:3ae626f3@news.devx.com...
> Hi there
>
> I have a SQL query similar to the following;
>
> select appnum, sum(assetcost), notes from contracts
> group by appnum, notes
>
> The notes column is of data type text. This statement fails because
columns
> with a data type of text cannot be part of a group by statement. The
> application has been in production for a year so I cannot change the notes
> field to a varchar() data type (nor do I want to, because the client
treats
> unlimited notes space as a big plus over their previous application). How
> can I get a Group By statement to work when one of the columns is data
type
> text?
>
> Thanks.
>
>