MAX (text) returns the text of the operand data type for the max operator is invalid. in SQL Server 2008

Question

I am using the text data type in one of my tables, and I am also using PIVOT with a query. I cannot use MAX(AttributeValue) , where AttributeValue is a type of text . It returns the following error Operand data type text is invalid for max operator. . How can I use it here because I am being forced to use the aggregate function with PIVOT .

Edit: I followed the post http://msdn.microsoft.com/en-us/library/ms187993.aspx

I tried converting the data type to nvarchar(max) .

 ALTER TABLE dbo.MyTable ALTER COLUMN AttributeValue NVARCHAR(MAX) 

I also need to use the Full Text Search parameter. I get the following error Cannot alter or drop column 'AttributeValue' because it is enabled for Full-Text Search.

 SELECT [6B93119B-263B-4FED-AA89-198D26A3A3C4] DOB ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] CaseTitle FROM MyTable PIVOT ( MAX(AttributeValue) FOR AttributeID IN ( [6B93119B-263B-4FED-AA89-198D26A3A3C4] ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] ) ) ResultTable 

Where "AttributeValue" has a data type of "text". I get the following error:

Operand type text is not valid for the max operator.

Well, I tried applying the field to nvarchar (max). This gives another type of error (in the fourth line).

 Incorrect syntax near '(' 

Did I miss something?

Answer 1

You can specify your text column in varchar (max).

 select max(cast(AttributeValue as varchar(max))) from YourTable 

You can convert your data into an additional request.

 SELECT [6B93119B-263B-4FED-AA89-198D26A3A3C4] DOB ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] CaseTitle FROM ( SELECT AttributeID, CAST(AttributeValue as VARCHAR(MAX)) as AttributeValue FROM MyTable ) AS T PIVOT ( MAX(AttributeValue) FOR AttributeID IN ( [6B93119B-263B-4FED-AA89-198D26A3A3C4] ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] ) ) ResultTable 

Answer 2

If what I remember is correct, the text in SQL Server does not allow you to run many commands against it. The only (half) solution I came across is translating text into another data type; unfortunately, this may mean writing some code and only processing the actual stored textual data in sections.

In addition, the text is removed from future versions of SQL Server: http://msdn.microsoft.com/en-us/library/ms187993.aspx

So, just change the column data type to NVarChar (Max), and hopefully the query will work properly.

Answer 3

You can also use DATALENGTH () in MAX () to avoid casting potentially very large strings.

 SELECT 6B93119B-263B-4FED-AA89-198D26A3A3C4] DOB ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] CaseTitle FROM MyTable PIVOT ( MAX(DATALENGTH(AttributeValue)) FOR AttributeID IN ( [6B93119B-263B-4FED-AA89-198D26A3A3C4] ,[F1A0D9D6-702E-4492-9EBC-63AD22E60E6A] ) ) ResultTable