Oracle Pivot-将值转换为列 [英] Oracle Pivot - converting values into columns

问题描述

我在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

以此类推.

到目前为止，我有:

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)
);

请参见带有演示的SQL小提琴

这篇关于Oracle Pivot-将值转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！