How to convert Oracle's pivot operator to MySQL

Oracle 11g introduced pivot operation that allows writing cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns and aggregate results. Pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that you want to get in the final output. SQL Server Migration Assistant (SSMA) for Oracle cannot correctly parse the pivot operator and generates the following error message: «Error O2SS0004: Unparsed SQL». In this blog post we will provide some useful workarounds and show how you can successfully avoid this error and convert Oracle’s pivot operation.

Possible Remedies

Consider the following example:

1
2
3
4
5
6
7
8
9
10

SELECT times_purchased, NY, CT, NJ, FL, MO, UNMAPPED
FROM
(SELECT times_purchased, state_code
FROM test_functions.customers)
pivot
(
COUNT(state_code) FOR state_code IN ('NY' AS NY, 'CT' AS CT, 'NJ' AS NJ,
'FL' AS FL, 'MO' AS MO, NULL AS unmapped)
)
ORDER BY times_purchased;

To solve the O2SS0004 error, you should perform the following actions.

put square brackets for pivot values which will be used as the column headings in our cross-tabulation query;
add aliases “t” and “PivotTable” for the statements which are specify what fields will be included in our cross tabulation;
remove aliases from pivot values declaration:[sql](‘NY’ as NY, ‘CT’ as CT, ….).[/sql]

So the updated SQL Server code doesn’t have any errors and looks as follows:

1
2
3
4
5
6
7
8
9

SELECT times_purchased, [NY], [CT], [NJ], [FL], [MO], [NULL] AS UNMAPPED
FROM
(SELECT times_purchased, state_code
FROM dbo.customers) t
pivot
(
COUNT(state_code) FOR state_code IN ([NY], [CT], [NJ], [FL], [MO], [NULL])
) PivotTable
ORDER BY CASE WHEN times_purchased IS NULL THEN 1 ELSE 0 END, times_purchased;

If the source code includes more than one aggregate in Oracle’s pivot operator, you will need to completely rewrite the query. Consider another example:

1
2
3
4
5
6
7
8
9
10

SELECT *
FROM
(SELECT cust_id, times_purchased, state_code
FROM test_functions.customers)
pivot
(
COUNT(state_code) AS cnt, MAX (cust_id) AS mx FOR state_code IN
('NY' AS NY, 'CT' AS CT, 'NJ' AS NJ, 'FL' AS FL, 'MO' AS MO, NULL AS unmapped)
)
ORDER BY times_purchased;

Converting this code to SQL Server, SSMA will generate exactly the same error message: «Error O2SS0004: Unparsed SQL», but the solution will be a bit harder. This time, to solve the O2SS0004 error, you should define the number of pivot values using CASE expression and group them by times_purchased field.

Conversion result

So the updated SQL Server code looks as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT times_purchased,
COUNT(CASE state_code WHEN 'NY' THEN 1 END) NY_CNT,
MAX(CASE state_code WHEN 'NY' THEN 1 END) NY_MX,
COUNT(CASE state_code WHEN 'CT' THEN 1 END) CT_CNT,
MAX(CASE state_code WHEN 'CT' THEN 1 END) CT_MX,
COUNT(CASE state_code WHEN 'NJ' THEN 1 END) NJ_CNT,
MAX(CASE state_code WHEN 'NJ' THEN 1 END) NJ_MX,
COUNT(CASE state_code WHEN 'FL' THEN 1 END) FL_CNT,
MAX(CASE state_code WHEN 'FL' THEN 1 END) FL_MX,
COUNT(CASE state_code WHEN 'MO' THEN 1 END) MO_CNT,
MAX(CASE state_code WHEN 'MO' THEN 1 END) MO_MX
FROM dbo.customers
GROUP BY times_purchased
ORDER BY CASE WHEN times_purchased IS NULL THEN 1 ELSE 0 END, times_purchased

Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!

Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.

Source of technical information: Oksana Eremenko, Technical Lead at DB Best.

Blog: Oracle to SQL Server migration with SSMA: How to convert Unparsed SQL — Pivot Operator

Possible Remedies

Conversion result

Related posts

Related posts

Getting started