Oracle SQL转置 [英] Oracle SQL Transpose
问题描述
在开始之前，我知道关于Stackoverflow的问题很多，但是我找不到与我的案子相关的任何问题，因为它们涉及的问题比我需要的复杂得多.
我想要的是一个简单的哑转置，不涉及逻辑.
这是我的选择查询返回的原始表:
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:如果您发现与我的确非常相似的问题，则可以将其标记为重复.
我建议先将UNPIVOT函数应用于您的多列，然后使用row_number()创建将在PIVOT中使用的新列名. /p>
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提琴.这将把您的多列数据转换成多行.我使用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提琴.这给出了最终结果:
| 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;
请参见带有演示的SQL小提琴
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
Typically, this is going to contain only 1 record i.e. for one person
Now what I want is
Field Value
Name A
Age 12
Sex M
DOB 8/7
Col1 aa
Col2 bb
Col3 cc
.
.
So there is no counting, summing or any complicated logic involved like most of the similar question on Stackoverflow.
How do I do it? I read through the PIVOT and UNPIVOT help and it was not that helpful at all.
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 .. .. .. ...
.
.
I want to know how to to do this for Oracle 10g and 11g
PS:Feel free to tag as duplicate if you find a question that is truly similar to mine.
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.
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;
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 |
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
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 |
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.
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;
这篇关于Oracle SQL转置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
