代码之家 › 专栏 › 技术社区 › Pinu

sql如何垂直转换数据

plsql oracle sql

Pinu · 技术社区 · 13 年前

我有一个3个日期的Dealer,payment_type和Dealer_payment_type

  Dealer : dealer_id , dealer_name, dealer_address
              1      | test      |  123 test lane
              2      | abc       |  abc lane  
              3      | def       |  def lane


 Payment_type : paymenttype_id , paytype 
                   1           | CHECK
                   2           | WIRE
                   3           | CREDIT

  Dealer_Payment_type : DPT_id , dealer_id , payment_type_id
                         1     |   1       | 1
                         2     |   1       | 2
                         3     |   1       | 3
                         4     |   2       | 2
                         5     |   2       | 3
                         6     |   3       | 1
                         7     |   3       | 2

我必须写一个查询来获得每个经销商的付款类型信息,查询需要返回这样的数据:

         dealer_id , dealer_name , paytype           
           1       |  test       | check,wire,credit
           2       |  abc        | wire,credit
           3       |  def        | check,wire

                    OR

      dealer_id , dealer_name  , check , wire , credit
       1        | test         | true  | true  | true 
       2        | abc          | false | true  | true 
       3        | def          | true  | false | true

1 回复 | 直到 13 年前

Taryn Frank Pearson 13 年前

您没有指定正在使用的Oracle版本。

如果您使用的是Oracle 11g,那么您可以使用以下内容。

要将值放入一列中,则可以使用 LISTAGG :

select d.dealer_id,
  d.dealer_name,
  listagg(p.paytype, ',') within group (order by d.dealer_id) as paytype
from dealer d
left join Dealer_Payment_type dp
  on d.dealer_id = dp.dealer_id
left join payment_type p
  on dp.payment_type_id = p.paymenttype_id
group by d.dealer_id, d.dealer_name;

看见 SQL Fiddle with demo

要获取单独列中的值,可以使用PIVOT:

select dealer_id, dealer_name,
  coalesce("Check", 'false') "Check",
  coalesce("Wire", 'false') "Wire",
  coalesce("Credit", 'false') "Credit"
from
(
  select d.dealer_id,
    d.dealer_name,
    p.paytype, 
    'true' flag
  from dealer d
  left join Dealer_Payment_type dp
    on d.dealer_id = dp.dealer_id
  left join payment_type p
    on dp.payment_type_id = p.paymenttype_id
)
pivot
(
  max(flag)
  for paytype in ('CHECK' as "Check", 'WIRE' as "Wire", 'CREDIT' as "Credit")
)

看见 SQL Fiddle with Demo .

如果您没有使用Oracle 11g,那么您可以使用 wm_concat() 要将值连接到一行中,请执行以下操作:

select d.dealer_id,
  d.dealer_name,
  wm_concat(p.paytype) as paytype
from dealer d
left join Dealer_Payment_type dp
  on d.dealer_id = dp.dealer_id
left join payment_type p
  on dp.payment_type_id = p.paymenttype_id
group by d.dealer_id, d.dealer_name;

要创建单独的列,可以使用具有 CASE :

select dealer_id, dealer_name,
  max(case when paytype = 'CHECK' then flag else 'false' end) "Check",
  max(case when paytype = 'WIRE' then flag else 'false' end) "Wire",
  max(case when paytype = 'CREDIT' then flag else 'false' end) "Credit"
from
(
  select d.dealer_id,
    d.dealer_name,
    p.paytype, 
    'true' flag
  from dealer d
  left join Dealer_Payment_type dp
    on d.dealer_id = dp.dealer_id
  left join payment_type p
    on dp.payment_type_id = p.paymenttype_id
)
group by dealer_id, dealer_name;

看见 SQL Fiddle with Demo