oracle sql 列值转列

Question

列值转列oraclesql

Answer 1

SELECT 
MAX(DECODE(TITLE,'姓名',TEXT)),
MAX(DECODE(TITLE,'年龄',TEXT)),
MAX(DECODE(TITLE,'爱好',TEXT))
FROM
(select 姓名 TEXT,'姓名' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN from T
union all
select 年龄 TEXT,'年龄' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN FROM T
union all
select 爱好 TEXT,'爱好' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN FROM T)
GROUP BY RN

Answer 2

忙中出错，试试这个
SELECT 
MAX(DECODE(RN,1,TEXT)),
MAX(DECODE(RN,2,TEXT)),
MAX(DECODE(RN,3,TEXT))
FROM
(select 姓名 TEXT,'姓名' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN from T
union all
select 年龄 TEXT,'年龄' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN FROM T
union all
select 爱好 TEXT,'爱好' AS TITLE,ROW_NUMBER()OVER (ORDER BY ROWID) RN FROM T)
GROUP BY TITLE

Answer 3

with t as
 (select 'zhangsan' nm, 8 age, 'rice' hobby
    from dual
  union all
  select 'lisi' nm, 9 age, 'play' hobby
    from dual
  union all
  select 'wangwu' nm, 10 age, 'moon' hobby
    from dual)
select *
  from (select nm from t) pivot(max(nm) for nm in('zhangsan' zhangsan,
                                                  'lisi' lisi,
                                                  'wangwu' wangwu))
union all
select *
  from (select nm, to_char(age) age from t) pivot(max(age) for nm in('zhangsan'
                                                                     zhangsan,
                                                                     'lisi' lisi,
                                                                     'wangwu'
                                                                     wangwu))
union all
select *
  from (select nm, hobby from t) pivot(max(hobby) for nm in('zhangsan'
                                                            zhangsan,
                                                            'lisi' lisi,
                                                            'wangwu' wangwu));