ERROR: Unsupported PIVOT column type: text

Question

I'm trying to perform a PIVOT but getting an error. I'm trying to see which patients have more than one Interval session. The column is a text type. I attempted to convert the column type to VARCHAR(MAX) in my query but still getting the error.

Here is my code:

SELECT patientname, IntervalName1, IntervalName2, IntervalName3
  FROM (SELECT DISTINCT 
               patientname,
               CAST(IntervalName AS VARCHAR(MAX)),
               'IntervalName' +
                CAST(ROW_NUMBER() OVER 
                    (PARTITION BY patientname
                         ORDER BY patientname) AS VARCHAR(10)) AS ColumnSequence
          FROM vwPatientPEIOutcomes
         WHERE [ vwPatientPEIOutcomes.DueDate = daterange ]
         GROUP BY patientname, IntervalName
         ORDER BY patientname, ColumnSequence) Temp
PIVOT(
      MAX(patientname) FOR 
         ColumnSequence IN ( IntervalName1, IntervalName2, IntervalName3 )
     ) tb2

Here are my second set of edits below in regards to your answer. I'm new to this so took my some time to figure out what was needed:

Thank you for the answer. I was using the group by because the row_number was creating duplicates and I could not figure out how else to not have that happen.

Both the patient name and interval name are strings in the database. Intervalname is a field that signifies if they have been administered a questionnaire in a given timeframe. I'm trying to create a single row per patient.

Here is the dataset:

CREATE TABLE vwPatientPEIOutcomes (
  patientname varchar(max) NOT NULL,
  intervalname varchar(max) NOT NULL,
  ) ;
   INSERT INTO vwpatientPEIOutcomes (patientname,intervalname) 
  VALUES
  ('Patient 1','End'),
  ('Patient 2','Start'),
  ('Patient 3','Start'),
  ('Patient 4','End'),
  ('Patient 4','6-Month'),
  ('Patient 4','Start'),
  ('Patient 5','6-Month'),
  ('Patient 6','Start'),
  ('Patient 6','End'),
  ('Patient 7','6-Month');

And I'm trying to get this result:

patientname	Intervalname1	Intervalname2	Intervalname3
Patient 1	End
Patient 2	Start
Patient 3	Start
Patient 4	End	6-Month	Start
Patient 5	6-Month
Patient 6	End	Start
Patient 7	6-Month

user_9937026 · Answer

The SQL engine is probably complaining about the reference in the GROUP BY clause. Change that to

GROUP BY patientname, CAST(IntervalName as varchar(max))

although, it's odd that you're using GROUP BY when there is no aggregation.

And I'm not sure the widow function you're using is in the correct context. The ORDER BY doesn't seem meaningful.

I could be sure if you had posted code to produce the table and sample data.

user_5841306 · Answer

You can use Conditional Aggregation after deriving ordinal values for each interval through use of ROW_NUMBER() window function within a subquery such as

SELECT patientname, 
       MAX(CASE WHEN rn = 1 THEN intervalname END) AS intervalname1,
       MAX(CASE WHEN rn = 2 THEN intervalname END) AS intervalname2,
       MAX(CASE WHEN rn = 3 THEN intervalname END) AS intervalname3
  FROM
  ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY patientname
                                      ORDER BY patientname) AS rn
      FROM vwPatientPEIOutcomes ) AS p
 GROUP BY patientname
 ORDER BY patientname

Demo

ERROR: Unsupported PIVOT column type: text

2 Answers