Oracle SQL 转置

2022-01-22 00:00:00 pivot sql oracle

在开始之前，我知道 Stackoverflow 上有很多关于这个主题的问题，但我找不到与我的案例相关的任何问题，因为它们涉及的东西比我需要的要复杂得多.

Before I begin, I know there is a whole bunch of questions on Stackoverflow on this topic but I could not find any of them relevant to my case because they involve something much more complicated than what I need.

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

What I want is a simple dumb transpose with no logic involved.

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

Here is the original table that my select query returns:

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

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

Typically, this is going to contain only 1 record i.e. for one person

现在我想要的是

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

因此没有像 Stackoverflow 上的大多数类似问题那样涉及计数、求和或任何复杂的逻辑.

So there is no counting, summing or any complicated logic involved like most of the similar question on Stackoverflow.

我该怎么做?我通读了 PIVOT 和 UNPIVOT 帮助，但一点帮助都没有.

How do I do it? I read through the PIVOT and UNPIVOT help and it was not that helpful at all.

PS:碰巧，如果它包含多个记录，是否可以将每个记录作为一个字段返回，有点像

PS: By chance, if it contains more than one records, is it possible to return each record as a field somewhat like

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

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

I want to know how to to do this for Oracle 10g and 11g

PS:如果您发现与我的问题真正相似的问题，请随意标记为重复.

PS:Feel free to tag as duplicate if you find a question that is truly similar to mine.

推荐答案

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

I would suggest applying the UNPIVOT function first to your multiple columns, then using row_number() to create your new column names that will be used in the PIVOT.

unpivot 的基本语法是

The basic syntax for the unpivot will be

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 with Demo.这会将您的多列数据转换为多行.我使用 row_number() 为您的新列名创建了一个唯一值，此查询中的数据如下所示:

See SQL Fiddle with Demo. This is going to convert your multiple columns of data into multiple rows. I used row_number() to create a unique value for your new column names, the data from this query looks like:

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

然后你可以对这个结果应用 PIVOT 函数:

Then you can apply the PIVOT function to this result:

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 with Demo.这给出了最终结果:

See SQL Fiddle with Demo. This gives a final result:

| 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 函数时，所有列的数据类型必须相同，因此您可能必须先在子查询中转换数据，然后才能对其进行 unpivot.

Note, when you are applying the unpivot function the datatype of all of the columns must be the same so you might have to convert your data in a subquery before you can unpivot it.

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

The UNPIVOT/PIVOT function were introduced in Oracle 11g, if you are using Oracle 10g, then you can edit the query to use:

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;

参见 SQL Fiddle with Demo

相关文章