在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

我希望将其转置(请注意，我不想转置合并")

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没有内置的 函数，例如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屋！