在 db2 中将行转换为列 [英] convert rows into column in db2

问题描述

我需要将以下选择结果转换为单行，

I need the below select results to be converted into single row ,

实际输出:

ORDER   POSTCODE    Quantity    Value
123456  AAAAA       22.78        5
123456  AAAAA       2.93         7

预期输出:

ORDER   POSTCODE    AmbientQuantity Ambientvalue FVQuantity FvVAlue
123456  AAAAA       22.78            5            2.93      7

如何在db2中达到预期的输出?

How to achieve the expected output in db2?

推荐答案

下面的 SQL 就完成了:

Following SQL will do the job:

with temp as (
select ORDER, POSTCODE, QUANTITY, VALUE,
       rownumber() over (partition by ORDER, POSTCODE) as rownum
  FROM mytable2
 ) 
select ORDER, POSTCODE,
       max(case when rownum = 1 Then QUANTITY end) as AMBIENTQUANTITY, 
       max(case when rownum = 1 Then VALUE end) as AMBIENTVALUE, 
       max(case when rownum = 2 Then QUANTITY end) as FVQuantity, 
       max(case when rownum = 2 Then VALUE end) as FVVALUE
  from temp
 group by ORDER, POSTCODE

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