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 ?
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