如何减少行和增加列以实现新视图

Question

我有一张类似于此的桌子

+=========+========+============+
| Session | Center | Efficiency |
+=========+========+============+
|       1 | A1     |         55 |
+---------+--------+------------+
|       1 | A2     |         66 |
+---------+--------+------------+
|       1 | A3     |         77 |
+---------+--------+------------+
|       2 | A1     |         80 |
+---------+--------+------------+
|       2 | A2     |         70 |
+---------+--------+------------+
|       2 | A3     |         60 |
+---------+--------+------------+

现在，我试图获得以下结果：

+=========+=========+=============+=========+=============+=========+=============+
| Session | Center1 | Efficiency1 | Center2 | Efficiency2 | Center3 | Efficiency3 |
+=========+=========+=============+=========+=============+=========+=============+
|       1 | A1      |          55 | A2      |          66 | A3      |          77 |
+---------+---------+-------------+---------+-------------+---------+-------------+
|       2 | A1      |          80 | A2      |          70 | A3      |          60 |
+---------+---------+-------------+---------+-------------+---------+-------------+

类似的，当我有这张桌子时，

+=========+========+============+
| Session | Center | Efficiency |
+=========+========+============+
|       1 | A1     |         55 |
+---------+--------+------------+
|       1 | A2     |         66 |
+---------+--------+------------+
|       1 | A3     |         77 |
+---------+--------+------------+
|       1 | A4     |         88 |
+---------+--------+------------+
|       2 | A1     |         80 |
+---------+--------+------------+
|       2 | A2     |         70 |
+---------+--------+------------+
|       2 | A3     |         60 |
+---------+--------+------------+
|       2 | A4     |         50 |
+---------+--------+------------+
|       3 | A1     |         56 |
+---------+--------+------------+
|       3 | A2     |         67 |
+---------+--------+------------+
|       3 | A3     |         78 |
+---------+--------+------------+
|       3 | A4     |         89 |
+---------+--------+------------+

我的输出应该是这样的：

+=========+=========+=============+=========+=============+=========+=============+=========+=============+
| Session | Center1 | Efficiency1 | Center2 | Efficiency2 | Center3 | Efficiency3 | Center4 | Efficiency4 |
+=========+=========+=============+=========+=============+=========+=============+=========+=============+
|       1 | A1      |          55 | A2      |          66 | A3      |          77 | A4      |          88 |
+---------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+
|       2 | A1      |          80 | A2      |          70 | A3      |          60 | A4      |          50 |
+---------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+
|       3 | A1      |          56 | A2      |          67 | A3      |          78 | A4      |          89 |
+---------+---------+-------------+---------+-------------+---------+-------------+---------+-------------+

为了得到这个，尝试了这个，

SELECT 
a.session as session, a.center as center1, a. Efficiency as Efficiency1, 
b.center as center2, b.Efficiency as Efficiency2 from
mytable a
JOIN
mytable b
on a.session=b.session AND a.center != b.center

但是并没有显示我想要得到的结果。它甚至显示了比以前更多的行，我不能正确地过滤掉行。如有任何建议，将不胜感激。谢谢。

Answer 1

如果您有一个可预测的、固定的center列表，则可以进行条件聚合：

select 
    session,
    'A1' Center1,
    max(case when center = 'A1' then efficiency end) Efficiency1,
    'A2' Center2,
    max(case when center = 'A2' then efficiency end) Efficiency2,
    'A3' Center3,
    max(case when center = 'A3' then efficiency end) Efficiency3
    -- more columns if needed...
from mytable
group by session

Answer 2

也许是这样的：

select distinct T.session,A1.*,A2.*,A3.*,A4.* from mytable as T
left outer join mytable AS A1 on A1.session=T.sessionand A1.center='A1'
left outer join mytable AS A2 on A2.session=T.sessionand A2.center='A2'
left outer join mytable AS A3 on A3.session=T.sessionand A3.center='A3'
left outer join mytable AS A4 on A4.session=T.sessionand A4.center='A4'

更新

select distinct T.session,
    A1.center as Center1,A1.Efficiency as Efficiency1,
    A2.center as Center2,A2.Efficiency as Efficiency2,
    A3.center as Center3,A3.Efficiency as Efficiency3,
    A4.center as Center4,A4.Efficiency as Efficiency4
...

Answer 3

对于使用不同DBMS的读者，或者如果h2实现了PIVOT，下面是一个简化的解决方案：

这是Server的语法

SELECT * FROM myTable PIVOT (MAX(efficiency) FOR center In (A1, A2, A3, A4)) as T
-- MAX is needed since PIVOT works with aggregate functions, but it should be MAX of a single value.

其结果是：

+---------+----+----+----+----+
| session | A1 | A2 | A3 | A4 |
+---------+----+----+----+----+
|       1 | 55 | 66 | 77 | 88 |
|       2 | 80 | 70 | 60 | 50 |
|       3 | 56 | 67 | 78 | 89 |
+---------+----+----+----+----+

如果需要，可以使查询动态化，并生成center列的列表(A1, A2, A3, A4)。

要为h2实现类似的功能，可以使用以下方法：

SELECT
    session,
    GROUP_CONCAT(CASE center WHEN '1A' THEN efficiency END) as 'efficiency_1A', 
    GROUP_CONCAT(CASE center WHEN '2A' THEN efficiency END) as 'efficiency_2A',
    GROUP_CONCAT(CASE center WHEN '3A' THEN efficiency END) as 'efficiency_3A',
    GROUP_CONCAT(CASE center WHEN '4A' THEN efficiency END) as 'efficiency_4A'
FROM myTable