拆分一行到基于列的值DB2多行(Split a row into multiple rows bas

我有以下数据：

Sr no....EMP_ID....Location...Period1....Value1....Period2....Value2....Period3....Value3
...1......Emp001....India........Salary.....10000.....Bonus.....1000.......Bonus code...AB
...2......Emp002....UK...........Salary.....20000.....Bonus.....2000.......Bonus code...CD

我想这样的输出：

Sr no....EMP_ID....Location....Period...Value
....1......Emp001....India.......Salary.....10000
....2......Emp001....India.......Bonus.....1000
....3......Emp001....India.......Bonus Code.....AB
....4......Emp002....UK.......Salary.....20000
....5......Emp002....UK.......Bonus.....2000
....6......Emp002....UK.......Bonus Code.....CD

Answer 1:

这是一个逆转置操作。 你可以做你想做一个什么union all ：

select EmpNo, Location, Period1 as period, Value1 as Value
from data
union all
select EmpNo, Location, Period2 as period, Value2 as Value
from data
union all
select EmpNo, Location, Period3 as period, Value3 as Value
from data;

有些数据库有unpivot的直接支持，以及其他的方法做。 然而，上面是ANSI标准SQL。

文章来源: Split a row into multiple rows based on a column value db2