Table Output in Sql

问题:

I have a query output as below.

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

I want to Arrange the above output as:

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

Please help me to achieve the above output.

回答1:

You can use row_number() to enumerate the prices. Then pivot the data. The following query does this using conditional aggregation:

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;

回答2:

Following Query will return your expected result regardless of DB2 version.

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

You may also try(depends on DB2 version):

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