Oracle Pivot - 将值转换为列 [英] Oracle Pivot - converting values into columns
本文介绍了Oracle Pivot - 将值转换为列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 Oracle 11g 中有 3 个字段的表:
I have table in Oracle 11g with 3 fields:
STUDYID | STUDY_PARAMETER | STUDY_VALUE
<小时>
5268 | Age Group | ADULT (18-65)
5269 | Age Group | ADULT (18-65)
5270 | Age Group | ADULT (18-65)
5271 | Age Unit | ADULT (18-65)
1668A | Trial Type | ADULT (18-65)
5273 | Trial Type | Dispensing
5345 | Age Unit | Years
1668AC | Age Group | ADULTS (18-39)
所以,我需要的是按以下顺序显示值:
So, what I need is to display values in this order:
STUDY_ID | AGE_GROUP | AGE_UNIT | TRIAL_TYPE
<小时>
5268 | ADULT (18-65) | Years | Dispensing
5269 | ADULT (18-65) | (null) | (null)
1668AC | ADULTS (18-39)| Years | Non - Dispensing
等等.
到目前为止我所拥有的是:
What I have so far is:
SELECT *
FROM (
SELECT STUDYID, STUDY_VALUE, STUDY_PARAMETER
FROM RD.STUDY_INFO
)
PIVOT (
SUM(STUDY_VALUE)
FOR (STUDY_PARAMETER)
IN (
'Age Unit' AS AGE_UNIT,
'Age Group' AS AGE_GROUP,
'Trial Type' AS TRIAL_TYPE
)
);
我从网上的例子中学到了这一点,但我不确定我是否可以像这样使用 SUM() ......?!
I learned this from examples on the net but I am not sure if I can use SUM() like this...?!
我收到此错误:
ORA-01722: invalid number
01722. 00000 - "invalid number"
有人看到我做错了什么吗?
Does anyone see what I am doing wrong?
推荐答案
由于 STUDY_VALUE
列显示为字符串,您将需要使用 max()
或 min()
对值的聚合函数:
Since the STUDY_VALUE
column appears to be a string, you will need to use either the max()
or min()
aggregate function on the values:
SELECT *
FROM
(
SELECT STUDYID, STUDY_VALUE, STUDY_PARAMETER
FROM STUDY_INFO
)
PIVOT
(
MAX(STUDY_VALUE)
FOR (STUDY_PARAMETER) IN ('Age Unit' AS AGE_UNIT,
'Age Group' AS AGE_GROUP,
'Trial Type' AS TRIAL_TYPE)
);
这篇关于Oracle Pivot - 将值转换为列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文