postgresql 行转列后加入到一个整体数据

得到的数据是：

使用max后：

SELECT  
    MAX( case when  SLIDE_SEQ = 1 THEN SLIDE_QTY  END  )as SLIDE_QTY1,
    MAX( case when  SLIDE_SEQ = 2 THEN SLIDE_QTY  END  )as SLIDE_QTY2,
    MAX( case when  SLIDE_SEQ = 3 THEN SLIDE_QTY  END  )as SLIDE_QTY3,
    MAX( case when  SLIDE_SEQ = 4 THEN SLIDE_QTY  END  )as SLIDE_QTY4,
    MAX( case when  SLIDE_SEQ = 5 THEN SLIDE_QTY  END  )as SLIDE_QTY5,
    MAX( case when  SLIDE_SEQ = 6 THEN SLIDE_QTY  END  )as SLIDE_QTY6,
    MAX( case when  SLIDE_SEQ = 7 THEN SLIDE_QTY  END  )as SLIDE_QTY7,
    MAX( case when  SLIDE_SEQ = 8 THEN SLIDE_QTY  END  )as SLIDE_QTY8,
    MAX( case when  SLIDE_SEQ = 9 THEN SLIDE_QTY  END  )as SLIDE_QTY9,
    MAX( case when  SLIDE_SEQ = 10 THEN SLIDE_QTY  END  )as SLIDE_QTY10
FROM
user
WHERE 
anken_id = ‘ADDM01-20160612-1-100002-001‘

得到的结果：

然后可以通过子查询进行组合

    SELECT * from  
m_product INNER JOIN 
    ( SELECT  
    anken_id,
        icd,
        MAX( case when  SLIDE_SEQ = 1 THEN SLIDE_QTY  END  )as SLIDE_QTY1,
        MAX( case when  SLIDE_SEQ = 2 THEN SLIDE_QTY  END  )as SLIDE_QTY2,
        MAX( case when  SLIDE_SEQ = 3 THEN SLIDE_QTY  END  )as SLIDE_QTY3,
        MAX( case when  SLIDE_SEQ = 4 THEN SLIDE_QTY  END  )as SLIDE_QTY4,
        MAX( case when  SLIDE_SEQ = 5 THEN SLIDE_QTY  END  )as SLIDE_QTY5,
        MAX( case when  SLIDE_SEQ = 6 THEN SLIDE_QTY  END  )as SLIDE_QTY6,
        MAX( case when  SLIDE_SEQ = 7 THEN SLIDE_QTY  END  )as SLIDE_QTY7,
        MAX( case when  SLIDE_SEQ = 8 THEN SLIDE_QTY  END  )as SLIDE_QTY8,
        MAX( case when  SLIDE_SEQ = 9 THEN SLIDE_QTY  END  )as SLIDE_QTY9,
        MAX( case when  SLIDE_SEQ = 10 THEN SLIDE_QTY  END  )as SLIDE_QTY10
    FROM
    user
WHERE 
user.anken_id = ‘ADDM01-20160612-1-100002-001‘
GROUP BY user.inner_cd,user.anken_id) t1 on  m_product.anken_id = t1.anken_id
where t1.anken_id = ‘ADDM01-20160612-1-100002-001‘

就可以得到富含行转列数据的。

postgresql 行转列后加入到一个整体数据

标签：