Pivot Table of Text

Question

I'm basing what I've done on this response: How to pivot text columns in SQL Server?

I have a table that looks like this.

InterviewID | QuestionNumber | PerformanceRatingComments
      1     |       1        |      Text1
      1     |       2        |      Text2
      2     |       1        |      Text3
      2     |       2        |      Text4
      2     |       3        |      Text3
      2     |       4        |      Text4

What I want to see is a table that shows

InterviewID | Q1PerfComments | Q2PerfComments | Q3PerfComments | Q4PerfComments.

I have tried this:

Select InterviewID, 
  pt.[1] as Q1PerfComments, 
  pt.[2] as Q2PerfComments, 
  pt.[3] as Q3PerfComments,
  pt.[4] as Q4PerfComments 
FROM 
(
    select InterviewID, QuestionNumber, PerformanceRatingComments
    from myTable
    WHERE
    PerformanceRatingComments IS NOT NULL
    and QuestionNumber >=1 
    And QuestionNumber <=4
 ) As Temp
PIVOT
(
   max(PerformanceRatingComments)
    FOR QuestionNumber In([1], [2], [3], [4])
) As pt

I'm getting an error that says:

Operand data type text is invalid for max operator.

What am I missing?

score 0 · Answer 1 · answered Nov 25 '13 at 15:01

Based on the error message:

Operand data type text is invalid for max operator.

You cannot apply an aggregate function to the text datatype. My suggestion would be to convert the PerformanceRatingComments to a varchar(max) before you PIVOT the data:

Select InterviewID, 
  pt.[1] as Q1PerfComments, 
  pt.[2] as Q2PerfComments, 
  pt.[3] as Q3PerfComments,
  pt.[4] as Q4PerfComments 
FROM 
(
    select InterviewID, QuestionNumber, 
      cast(PerformanceRatingComments as varchar(max)) PerformanceRatingComments
    from myTable
    WHERE PerformanceRatingComments IS NOT NULL
      and QuestionNumber >=1 
      And QuestionNumber <=4
 ) As Temp
PIVOT
(
   max(PerformanceRatingComments)
    FOR QuestionNumber In([1], [2], [3], [4])
) As pt;

See SQL Fiddle with Demo

Pivot Table of Text

1 Answers1