Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
    type int,
    code text,
    "desc" text,
    store int,
    sales_day int,
    stock int
);

INSERT INTO mytable VALUES
(1, 'AA1', 'abc', 101, 3, 6),
(1, 'AA2', 'abd', 101, 4, 0),
(1, 'AA3', 'abf', 101, 4, 3),
(2, 'BA1', 'bba', 101, 5, 1),
(2, 'BA2', 'bbc', 101, 2, 1),
(1, 'AA1', 'abc', 102, 1, 4),
(1, 'AA2', 'abd', 102, 2, 0),
(2, 'BA1', 'bba', 102, 4, 2),
(2, 'BA2', 'bbc', 102, 5, 5);


SELECT * FROM mytable;
9 rows affected



type
code
desc
store
sales_day
stock


1
AA1
abc
101
3
6

1
AA2
abd
101
4
0

1
AA3
abf
101
4
3

2
BA1
bba
101
5
1

2
BA2
bbc
101
2
1

1
AA1
abc
102
1
4

1
AA2
abd
102
2
0

2
BA1
bba
102
4
2

2
BA2
bbc
102
5
5


SELECT
    type,
    code,
    "desc",
    COALESCE(SUM(sales_day) FILTER (WHERE store = 101)) as sales_day_101,
    COALESCE(SUM(stock) FILTER (WHERE store = 101), 0) as stock_101,
    COALESCE(SUM(sales_day) FILTER (WHERE store = 102), 0) as sales_day_102,
    COALESCE(SUM(stock) FILTER (WHERE store = 102), 0) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code


type
code
desc
sales_day_101
stock_101
sales_day_102
stock_102


1
AA1
abc
3
6
1
4

1
AA2
abd
4
0
2
0

1
AA3
abf
4
3
0
0

2
BA1
bba
5
1
4
2

2
BA2
bbc
2
1
5
5


SELECT
    type,
    code,
    "desc",
    SUM(CASE WHEN store = 101 THEN sales_day ELSE 0 END) as sales_day_101,
    SUM(CASE WHEN store = 101 THEN stock ELSE 0 END) as stock_101,
    SUM(CASE WHEN store = 102 THEN sales_day ELSE 0 END) as sales_day_102,
    SUM(CASE WHEN store = 102 THEN stock ELSE 0 END) as stock_102
FROM mytable
GROUP BY type, code, "desc"
ORDER BY type, code


type
code
desc
sales_day_101
stock_101
sales_day_102
stock_102


1
AA1
abc
3
6
1
4

1
AA2
abd
4
0
2
0

1
AA3
abf
4
3
0
0

2
BA1
bba
5
1
4
2

2
BA2
bbc
2
1
5
5

type	code	desc	store	sales_day	stock
1	AA1	abc	101	3	6
1	AA2	abd	101	4	0
1	AA3	abf	101	4	3
2	BA1	bba	101	5	1
2	BA2	bbc	101	2	1
1	AA1	abc	102	1	4
1	AA2	abd	102	2	0
2	BA1	bba	102	4	2
2	BA2	bbc	102	5	5

type	code	desc	store	sales_day	stock
1	AA1	abc	101	3	6
1	AA2	abd	101	4	0
1	AA3	abf	101	4	3
2	BA1	bba	101	5	1
2	BA2	bbc	101	2	1
1	AA1	abc	102	1	4
1	AA2	abd	102	2	0
2	BA1	bba	102	4	2
2	BA2	bbc	102	5	5

type	code	desc	store	sales_day	stock
1	AA1	abc	101	3	6
1	AA2	abd	101	4	0
1	AA3	abf	101	4	3
2	BA1	bba	101	5	1
2	BA2	bbc	101	2	1
1	AA1	abc	102	1	4
1	AA2	abd	102	2	0
2	BA1	bba	102	4	2
2	BA2	bbc	102	5	5