postgresql 行轉列後加入到一個整體資料

標籤：

這裡行轉列的基本思想就是使用max，因為其他列下面都是NULL,所以可以Max最後就只能得到有值的這行

普通的查詢：

SELECT icd , case when (ROW_NUMBER() OVER(PARTITION BY INNER_CD ORDER BY SLIDE_SEQ )) =1  then  SLIDE_QTY  END  as SLIDE_QTY1, case when                                                                                                                     (ROW_NUMBER() OVER(PARTITION BY INNER_CD ORDER BY SLIDE_SEQ )) =1 then SLIDE_S_UNIT_PRICE END   as SLIDE_S_UNIT_PRICE1, case when                                                                                                                     (ROW_NUMBER() OVER(PARTITION BY INNER_CD ORDER BY SLIDE_SEQ )) =2 then SLIDE_QTY END   as SLIDE_QTY2, case when                                                                                                                     (ROW_NUMBER() OVER(PARTITION BY INNER_CD ORDER BY SLIDE_SEQ )) =3 then SLIDE_QTY END  as SLIDE_QTY3FROM    "temp"."user" mmWHERE mm.icd = ‘ADDM01-20160612-1-100002-001‘

得到的資料是：

使用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_QTY10FROMuserWHERE 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    userWHERE user.anken_id = ‘ADDM01-20160612-1-100002-001‘GROUP BY user.inner_cd,user.anken_id) t1 on  m_product.anken_id = t1.anken_idwhere t1.anken_id = ‘ADDM01-20160612-1-100002-001‘

就可以得到富含行轉列資料的。

postgresql 行轉列後加入到一個整體資料