具有给定表数据的所需输出 [英] Desired output with given table data

问题描述

我有一个表测试，其结构如下:

Hi I have a table test its structure is given below:

**Testing**

  PK    C1    c2  
 ---------------
 1      v11  v12
 2      v21  v23
 3      v31  v32

现在我需要查询该表(测试)，以便获得以下输出.

Now I need to query this table (testing) such that I get the below output.

 Pk  Key value 
---------------
 1    c1    v11
 1    c1    v12
 2    c2    v21
 2    c2    v22
 3    c3    v31
 3    c3    v32

Oracle 11g中的sql查询是否可以实现，11g中的PIVOT功能是否可以实现?

Can this been possible with sql query in Oracle 11g ,Is it possible with PIVOT feature in 11g?

推荐答案

否，不能使用PIVOT完成，但是可以使用

No, it can't be done with PIVOT, but it can be done with UNPIVOT:

SELECT
  Pk,
  "Key",
  value
FROM Testing
UNPIVOT (
  value FOR "Key" IN (C1, C2)
)

而当UNPIVOT不可用时，我通常会像这样取消透视:

And when UNPIVOT is unavailable, I often unpivot like this:

SELECT
  t.Pk,
  x."Key",
  CASE x."Key"
    WHEN 'C1' THEN t.C1
    WHEN 'C2' THEN t.C2
  END AS value
FROM Testing t
CROSS JOIN (
  SELECT 'C1' AS "Key" FROM DUAL UNION ALL
  SELECT 'C2' FROM DUAL
) x

这篇关于具有给定表数据的所需输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！