Postgres在数组和crosstab方面有一些高级功能，但是，一种独立于数据库的方法是使用聚合：

select t.p,
       max(case when c = 3 then a end) as a3,
       max(case when c = 3 then b end) as b3,
       max(case when c = 4 then a end) as a4,
       max(case when c = 4 then b end) as b4
from atable t
group by t.p;

这将在SQLite和Postgres（以及几乎任何其他数据库）中工作。

测试数据

DECLARE @TABLE TABLE (id INT, P INT, C INT, A VARCHAR(2), B VARCHAR(2))
INSERT INTO @TABLE VALUES 
(1  ,100 ,3  ,'a1','b1'),
(2  ,101 ,3  ,'a2','b2'),
(3  ,102 ,3  ,'a3','b3'),
(4  ,103 ,3  ,'a4','b4'),
(5  ,100 ,4  ,'a5','b5'),
(6  ,101 ,4  ,'a6','b6'),
(7  ,102 ,4  ,'a7','b7'),
(8  ,103 ,4  ,'a8','b8')

查询

SELECT * FROM 
 (
    SELECT P , Vals , '_' + CAST(C AS VARCHAR(10)) + N  AS Cols
    FROM @TABLE 
        UNPIVOT (Vals FOR N IN (A, B))up
 )A
 PIVOT (MAX(Vals)
        FOR Cols 
        IN ([_3A],[_3B],[_4A],[_4B])
        )p

结果

╔═════╦═════╦═════╦═════╦═════╗
║  P  ║ _3A ║ _3B ║ _4A ║ _4B ║
╠═════╬═════╬═════╬═════╬═════╣
║ 100 ║ a1  ║ b1  ║ a5  ║ b5  ║
║ 101 ║ a2  ║ b2  ║ a6  ║ b6  ║
║ 102 ║ a3  ║ b3  ║ a7  ║ b7  ║
║ 103 ║ a4  ║ b4  ║ a8  ║ b8  ║
╚═════╩═════╩═════╩═════╩═════╝