SELECT * FROM T
/*
1 1 001 10.00
2 1 002 20.00
3 2 001 10.00
4 2 002 10.00
5 3 001 10.00
*/CREATE VIEW t_view AS
SELECT
ID
,'001' AA1
,(SELECT BB FROM T WHERE ID = a.ID AND AA = '001') BB1
,'002' AA2
,NVL((SELECT BB FROM T WHERE ID = a.ID AND AA = '002'),0.00) BB2
FROM T a
GROUP BY ID
/select * from t_view
/*
ID AA1 BB1 AA2 BB2
1 001 10 002 20
2 001 10 002 10
3 001 10 002 0
*/
/*
1 1 001 10.00
2 1 002 20.00
3 2 001 10.00
4 2 002 10.00
5 3 001 10.00
*/CREATE VIEW t_view AS
SELECT
ID
,'001' AA1
,(SELECT BB FROM T WHERE ID = a.ID AND AA = '001') BB1
,'002' AA2
,NVL((SELECT BB FROM T WHERE ID = a.ID AND AA = '002'),0.00) BB2
FROM T a
GROUP BY ID
/select * from t_view
/*
ID AA1 BB1 AA2 BB2
1 001 10 002 20
2 001 10 002 10
3 001 10 002 0
*/
SELECT
ID
,'001' AA1
,to_char((SELECT BB FROM T WHERE ID = a.ID AND AA = '001'),99.99) BB1
,'002' AA2
,to_char(NVL((SELECT BB FROM T WHERE ID = a.ID AND AA = '002'),'0.00'),99.99) BB2
FROM T a
GROUP BY ID