在SQL表输出(Table Output in Sql)

我有如下的查询输出。

Name   Price ProductTypeCode
Ram    120   P1
RAM    130   P1
RAM    140   P1
RAM    240   P1
RAM    340   P1
RAM    190   P2
RAM    160   P2

我想尽可能安排上面的输出：

Name   P2Price P1Price
Ram    190     120
RAM    160     130
RAM    null    140
RAM    null    240
RAM    null    340

请帮我实现上述输出。

Answer 1:

您可以使用row_number()来枚举价格。 然后转动的数据。 下面的查询执行此操作使用条件汇总：

select name,
       max(case when producttypecode = 'p2' then price end) as p2price,
       max(case when producttypecode = 'p1' then price end) as p1price
from (select t.*,
             row_number() over (partition by name, producttypecode order by name) as seqnum
      from table t
     ) t
group by name, seqnum;

Answer 2:

下面的查询将返回您预期的结果，无论DB2版本。

SELECT name,
       CASE WHEN producttypecode = 'p2' THEN price END AS p2price,
       CASE WHEN producttypecode = 'p1' THEN price END AS p1price  
       FROM YourTable

你也可以尝试（取决于DB2版本）：

SELECT NAME,
   DECODE(producttypecode , 'p2', price) AS p2pricel,
   DECODE(producttypecode , 'p1', price) AS p1price
FROM YourTable

文章来源: Table Output in Sql