将列转为行(oracle) [英] Unpivot columns into rows (oracle)

问题描述

很像这个原始的 SO 在 Oracle 中将行动态转为列

much like this original SO Pivoting rows into columns dynamically in Oracle

但我想做相反的事情

我怎样才能得到

 ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married

进入这个

ID       K       V
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

推荐答案

您正在寻找 unpivot.

select * from t 
unpivot (
          v for k in ("NAME","AGE","GENDER","STATUS")
        ) u

如果年龄列是整数，您可能会遇到类型不匹配的情况.在这种情况下，在取消旋转之前将其转换为字符.

You may have a type mismatch if the age column is an integer. In that case convert it to a character before unpivoting.

select *
from (select id,name,to_char(age) age,gender,status from t) t 
unpivot (
         v for k in ("NAME","AGE","GENDER","STATUS")
        ) u

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