sql - 在sql中将表从垂直转移到水平

Question

我想从

看起来像这样：

不知道如何接近？

我的代码是这个

FROM TABLE A, 
(SELECT PART_NO, SUM(QUANTITY) AS Type_1
FROM TABLE
WHERE STYPE = 'Type_1'
GROUP BY PART_NO, STYPE) B, 
(SELECT PART_NO, SUM(QUANTITY) AS Type_2
FROM TABLE
WHERE STYPE = 'Type_2'
GROUP BY PART_NO, STYPE) C,
(SELECT PART_NO, SUM(QUANTITY) AS Type_3
FROM TABLE
WHERE STYPE = 'Type_3'
GROUP BY PART_NO, STYPE) D,
(SELECT PART_NO, SUM(QUANTITY) AS Type_4
FROM TABLE
WHERE STYPE = 'Type_4'
GROUP BY PART_NO, STYPE) E
WHERE A.PART_NO = B.PART_NO
AND A.PART_NO = C.PART_NO
AND A.PART_NO = D.PART_NO
AND A.PART_NO = E.PART_NO
AND A.PART_NO = F.PART_NO
GROUP BY A.PART_NO, B.Type_1, C.Type_2, D.Type_2, E.Type_4

但它会删除带有 nan 的行。不知道我在哪里做错了。

标签： sqldata-transform

Answer 1

这是一种可以使用条件聚合生成的旋转类型。

例如，您可以这样做：

select
  part_no,
  sum(case when stype = 'Type 1' then quantity end) as type_1,
  sum(case when stype = 'Type 2' then quantity end) as type_2,
  sum(case when stype = 'Type 3' then quantity end) as type_3
from my_table
group by part_no