数据透视或不透视查询结果？

160

多利安普

我是Oracle中令人困惑的枢轴运算符和非枢轴运算符的新手。有人可以帮助我调整这样的查询结果吗？

SELECT * FROM
(
    SELECT
    ROUND(((SELECT COUNT(*) FROM TABLE_X WHERE X = 1) / (SELECT (COUNT(*)) FROM TABLE_X) * 100))AS X_FIELD,
    ROUND(((SELECT COUNT(*) FROM TABLE_Y WHERE Y = 1) / (SELECT (COUNT(*)) FROM TABLE_Y) * 100))AS Y_FIELD FROM DUAL

);

结果是这样的：

X_FIELD        Y_FIELD
----------     ----------
37             26

我需要这样的东西：

FIELDS         PERCENTAGE
----------     ----------
X_FIELD        37 
Y_FIELD        26

请指导我如何实现这一目标。

Olesya Razuvayevskaya

试试这个：

WITH temp_result AS
  (SELECT ROUND((
    (SELECT COUNT(*) FROM TABLE_X WHERE X = 1) /(SELECT (COUNT(*)) FROM TABLE_X) * 100))AS X_FIELD,
    ROUND(((SELECT COUNT(*) FROM TABLE_Y WHERE Y = 1) /(SELECT (COUNT(*)) FROM TABLE_Y) * 100))AS Y_FIELD
  FROM DUAL
  )
SELECT *
FROM temp_result UNPIVOT INCLUDE NULLS ( VALUE FOR COL IN (X_FIELD,Y_FIELD));

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。