MAX(text) returns Operand data type text is invalid for max operator. in sql server 2008

Question

I use text data type in one of my tables and also I use PIVOT with the query too. I am unable to use MAX(AttributeValue) where AttributeValue is the 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 imposed to use an aggregate function with PIVOT.

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

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

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

Also I have to use Full Text Search option too. 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 the 'AttributeValue' is of 'text' data type. I get the following error,

Operand data type text is invalid for max operator.

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

Incorrect syntax near '('

Did I miss anything ?

Answer 1

You can cast your text column to varchar(max).

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

You can convert your data in a sub query.

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, text in SQL Server doesn't let you run a lot of commands against it. The only (half) solution I have come across is casting text to a different data type; unfortunately, this can mean writing some code, and only dealing with the actual stored text data in sections.

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

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