将行转换为db2中的列 [英] convert rows into column in db2
本文介绍了将行转换为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屋!
查看全文