Oracle SQL转置

小编典典

Oracle SQL转置

sql

在我开始之前，我知道问题很多，但是我找不到与我的案子相关的任何问题，因为它们涉及的问题比我需要的复杂得多。

我想要的是一个简单的哑转置，不涉及逻辑。

这是我的选择查询返回的原始表：

Name Age Sex DOB Col1 Col2 Col3 ....  
A    12  M   8/7 aa   bb   cc

通常，这将仅包含1条记录，即一个人的记录

现在我想要的是

Field   Value
Name    A
Age     12
Sex     M
DOB     8/7
Col1    aa
Col2    bb
Col3    cc
.
.

因此，无需像Stackoverflow上的大多数类似问题那样进行计数，求和或任何复杂的逻辑。

我该怎么做？我通读了PIVOT和UNPIVOT的帮助，但那根本没有帮助。

PS：如果偶然包含多个记录，是否有可能将每个记录作为字段返回，就像

Field   Value1   Value2   Value3 ...
Name    A        B        C      ...
Age     ..       ..       ..     ...
.
.

我想知道如何针对Oracle 10g和11g执行此操作

PS：如果您发现与我的确很相似的问题，可以随意将其标记为重复。

阅读 202

2021-04-14

共1个答案

小编典典

我建议先将UNPIVOT函数应用于您的多列，然后再row_number()用于创建将在PIVOT中使用的新列名。

unpivot的基本语法为

select field, 
  value,
  'value'||
   to_char(row_number() over(partition by field
                              order by value)) seq
from yourtable
unpivot
(
  value
  for field in (Name, Age, Sex, DOB, col1, col2, col3)
) u;

请参阅带有演示的SQL
Fiddle。这将把您的多列数据转换成多行。我曾经row_number()为您的新列名创建一个唯一值，该查询中的数据如下所示：

| FIELD |                   VALUE |    SEQ |
|-------|-------------------------|--------|
|   AGE |                      12 | value1 |
|   AGE |                      15 | value2 |
|  COL1 |                      aa | value1 |
|  COL1 |                      xx | value2 |

然后，您可以将PIVOT函数应用于此结果：

select field, value1, value2
from
(
  select field, 
    value,
    'value'||
      to_char(row_number() over(partition by field
                                order by value)) seq
  from yourtable
  unpivot
  (
    value
    for field in (Name, Age, Sex, DOB, col1, col2, col3)
  ) u
) d
pivot
(
  max(value)
  for seq in ('value1' as value1, 'value2' as value2)
) piv

请参阅带有演示的SQL Fiddle。这给出了最终结果：

| FIELD |                  VALUE1 |                  VALUE2 |
|-------|-------------------------|-------------------------|
|   AGE |                      12 |                      15 |
|  COL1 |                      aa |                      xx |
|  COL2 |                      bb |                      yy |
|  COL3 |                      cc |                      zz |
|   DOB | 07-Aug-2001 12:00:00 AM | 26-Aug-2001 12:00:00 AM |
|  NAME |                       A |                       B |
|   SEX |                       F |                       M |

请注意，当您应用unpivot函数时，所有列的数据类型必须相同，因此您可能必须先在子查询中转换数据，然后才能取消数据透视。

Oracle 11g中引入了UNPIVOT / PIVOT函数，如果您使用的是Oracle 10g，则可以编辑查询以使用：

with cte as
(
  select 'name' field, name value
  from yourtable
  union all
  select 'Age' field, Age value
  from yourtable
  union all
  select 'Sex' field, Sex value
  from yourtable
  union all
  select 'DOB' field, DOB value
  from yourtable
  union all
  select 'col1' field, col1 value
  from yourtable
  union all
  select 'col2' field, col2 value
  from yourtable
  union all
  select 'col3' field, col3 value
  from yourtable
)
select
  field,
  max(case when seq = 'value1' then value end) value1,
  max(case when seq = 'value2' then value end) value2
from
(
  select field, value,
  'value'||
      to_char(row_number() over(partition by field
                                order by value)) seq
  from cte
) d
group by field;

2021-04-14