在 DB2 中旋转 [英] Pivoting in DB2

问题描述

我必须将行转换为 DB2 表中的列.这就是我的表的结构..

I have to transpose my rows into columns from a DB2 table.This is how my table is structured..

ItemID    Item    Value
---------------------
1     Meeting     Now
1     Advise      Yes
1     NoAdvise    No
2     Meeting     Never
2     Advise      No
2     NoAdvise    Null
2     Combine    Yes

我希望将其转置为(注意我不想转置 Combine)

I want this to be transposed into(note that I do not want to transpose Combine)

ItemID    Meeting  Advise   NoAdvise 
---------------------------------------
1         Now      Yes       No
2         Never    No        Null

查询有点困难，您能帮忙吗?

Bit struggling with the query, can you please help?

推荐答案

它不是很漂亮，但应该可以.DB2 没有内置 PIVOT 函数，如 SQL Server.

It's not very pretty, but it should work. DB2 doesn't have a built-in PIVOT function, like SQL Server.

SELECT DISTINCT
     A.ItemID
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Meeting'
    ) AS Meeting
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'Advise'
    ) AS Advise
    ,(SELECT value
      FROM table B
      WHERE B.ItemID = A.ItemID
        AND B.Item   = 'NoAdvise'
    ) AS NoAdvise
FROM table A

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