纵表转横表

lanqilin5186

于 2014-03-24 16:35:59 发布

阅读量108

点赞数

分类专栏： Oracle 文章标签： sql

本文链接：https://blog.csdn.net/lanqilin5186/article/details/84540762

版权

Oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

纵表数据

MGMT_ENTITY_ID ATTR_NAME ATTR_VALUE

-------------- ---------------------- ---------------

1000 uuid spcos2

1020 hypervisor OpenStack

1030 vlanId 111

2000 uuid xian

2020 hypervisor OpenStack

2030 vlanId 111

301 cpunum 8

301 disk 1000

---------  纵表转横表
-- 此语句支持查询了符合参数条件的 MGMT_ENTITY_ATTRIBUTE中的MGMT_ENTITY_ID 


select t1.MGMT_ENTITY_ID ,partyId from  (
    select met.MGMT_ENTITY_ID ,
    sum(case met.ATTR_NAME when 'partyId' then met.ATTR_VALUE else null end)  partyId,
    sum(case met.ATTR_NAME when 'isBand' then met.ATTR_VALUE else null end)  isBand,
    sum(case met.ATTR_NAME when 'pdcId' then met.ATTR_VALUE else null end)  pdcId
    from MGMT_ENTITY_ATTRIBUTE met
  group by met.MGMT_ENTITY_ID) t1 
where t1.partyId='127612'
and  t1.isBand='123'
and  t1.pdcId='1000'