Oracle to SQL Server migration with SSMA: Converting unparsed SQL — Pivot Operator

13 Oct 2021 Andrey Khudyakov (DB architect), Roman Muzyka (writer)

The pivot clause is an integral part of the Oracle database. In particular, the pivot_clause of the SELECT statement allows you to write crosstabulation queries. Here you can rotate rows into columns and aggregate data in the rotation process. As an important data warehousing element, pivot rotates a table-value expression. To be more specific, it turns the unique value from one expression column into multiple output columns. Besides, pivot performs aggregations where they are needed on any of the remaining column values that you need to get in the final output.

Unfortunately, SQL Server Migration Assistant (SSMA) cannot correctly parse the Oracle pivot operator. Instead, it generates the following error message: “O2SS0561: Conversion of PIVOT clause is not supported.” This article looks into appropriate solutions that will help you avoid this error and successfully convert Oracle pivot operation.

Possible solutions

Let’s look at 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;

If you want to find a solution to the O2SS0561 error, follow this step-by-step approach:

Put square brackets for pivot values; in a cross-tabulation query they will be used as the column headings.

Add aliases “PivotTable” and “t” for the statements that specify the content of our cross-tabulation.

Remove aliases from the following pivot values declaration: [sql](‘NY’ as NY, ‘CT’ as CT, ….).[/sql].

By following these steps, you eliminate all errors from the updated SQL Server code.

You receive the following code:

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 you see that there is more than one aggregate in Oracle’s pivot operator included in the source code, rewrite the query completely.

Let’s take a look at one more 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;

If you try to convert this code to SQL Server, you will receive the same error message from SSMA: “O2SS0561: Conversion of PIVOT clause is not supported.” This problem will be a bit more challenging to solve than the previous one. To cope with this problem, define the number of pivot values that use the CASE expression. After that, you should group them by the times_purchased field.

As a result, you will receive the following SQL Server code:

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 cust_id END) NY_MX,
COUNT(CASE state_code WHEN 'CT' THEN 1 END) CT_CNT,
MAX(CASE state_code WHEN 'CT' THEN cust_id END) CT_MX,
COUNT(CASE state_code WHEN 'NJ' THEN 1 END) NJ_CNT,
MAX(CASE state_code WHEN 'NJ' THEN cust_id END) NJ_MX,
COUNT(CASE state_code WHEN 'FL' THEN 1 END) FL_CNT,
MAX(CASE state_code WHEN 'FL' THEN cust_id END) FL_MX,
COUNT(CASE state_code WHEN 'MO' THEN 1 END) MO_CNT,
MAX(CASE state_code WHEN 'MO' THEN cust_id 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

Final thoughts

Did you find this article useful? Please do not hesitate to contact ABCloudz or check our Jumpstart for the SSMA offer if you have more questions or want to start your database migration project.

Also, make sure to review our blog section for the series of blog posts dedicated to the most common SSMA Oracle to SQL Server migration issues.