db2 - 在 DB2 的一行中显示列值

Question

有没有办法在一行中显示一些列值，然后是剩余的列？我已经编写了以下查询，它提供了 3 行作为输出，但它应该只带来 1 个输出。

select asset.assetnum, asset.serialnum, assetspec.alnvalue , assetspec.assetattrid  
from asset  
left outer join  assetspec on asset.assetnum = assetspec.assetnum  
and asset.classstructureid = assetspec.classstructureid  
where asset.assetnum='100' AND assetspec.ASSETATTRID IN ('XXX','YYY','ZZZ')

输出

ASSETNUM   SERIALNUM    ALNVALUE  ASSETATTRID
100            123         A         XXX
100            123         B         YYY
100            123         C         ZZZ 

预期输出

ASSETNUM   SERIALNUM    XXX    YYY   ZZZ
 100          123         A      B    C

标签： db2

Answer 1

尝试这个：

with tab (ASSETNUM, SERIALNUM, ALNVALUE, ASSETATTRID) as (values
  (100, 123, 'A', 'XXX')
, (100, 123, 'B', 'YYY')
, (100, 123, 'C', 'ZZZ')
)
select ASSETNUM, SERIALNUM
, max(case ASSETATTRID when 'XXX' then ALNVALUE end) as XXX
, max(case ASSETATTRID when 'YYY' then ALNVALUE end) as YYY
, max(case ASSETATTRID when 'ZZZ' then ALNVALUE end) as ZZZ
from tab
group by ASSETNUM, SERIALNUM;