CODEWITHSUNDEEP
sqloracle11ggroup-bypivot
user2107364
user2107364

Reputation: 11

Oracle; Create custom pivot from table

Ich have this:

ZN  BTG     BST FS  IST         D
161 18262   BC  An  1577861387  1,8
161 18262   BC  Ab  1577861875  3,9
161 18262   FK  An  1577862769  3,8
161 18262   FK  Ab  1577863033  0,2
161 18262   B   An  1577863670  -0,2
161 18262   B   Ab  1577863866  1,1

But i want this:

ZN  BTG   BC An      BC An  BC Ab      BC Ab  Fk An      Fk An  FK Ab  FK Ab  B An  B An  B Ab  B Ab
161 18262 1577861387 1,8    1577861875 3,9    1577862769 3,8  ......

I don't know where to start. Thanks.

Upvotes: 0

Views: 30

Answers (2)

MT0
MT0

Reputation: 167981

Assuming you have a fixed set of values the you can use a PIVOT with multiple keys:

SELECT *
FROM   table_name
PIVOT ( MAX(ist) AS ist, MAX(d) AS d FOR (bst, fs) IN (
  ('BC', 'An') AS bc_an,
  ('BC', 'Ab') AS bc_ab,
  ('FK', 'An') AS fk_an,
  ('FK', 'Ab') AS fk_ab,
  ('B',  'An') AS b_an,
  ('B',  'Ab') AS b_ab
) )

So for your test data:

CREATE TABLE table_name ( ZN, BTG, BST, FS, IST, D ) AS
SELECT 161, 18262, 'BC', 'An', 1577861387, '1,8'  FROM DUAL UNION ALL
SELECT 161, 18262, 'BC', 'Ab', 1577861875, '3,9'  FROM DUAL UNION ALL
SELECT 161, 18262, 'FK', 'An', 1577862769, '3,8'  FROM DUAL UNION ALL
SELECT 161, 18262, 'FK', 'Ab', 1577863033, '0,2'  FROM DUAL UNION ALL
SELECT 161, 18262, 'B',  'An', 1577863670, '-0,2' FROM DUAL UNION ALL
SELECT 161, 18262, 'B',  'Ab', 1577863866, '1,1'  FROM DUAL

This outputs:

 ZN |   BTG |  BC_AN_IST | BC_AN_D |  BC_AB_IST | BC_AB_D |  FK_AN_IST | FK_AN_D |  FK_AB_IST | FK_AB_D |   B_AN_IST | B_AN_D |   B_AB_IST | B_AB_D
--: | ----: | ---------: | :------ | ---------: | :------ | ---------: | :------ | ---------: | :------ | ---------: | :----- | ---------: | :-----
161 | 18262 | 1577861387 | 1,8     | 1577861875 | 3,9     | 1577862769 | 3,8     | 1577863033 | 0,2     | 1577863670 | -0,2   | 1577863866 | 1,1

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can use conditional aggregation:

select zn, btg,
       max(case when bst = 'BC' and fs = 'An' then ist end) as bc_an_ist
       max(case when bst = 'BC' and fs = 'An' then d end) as bc_an_d,
       max(case when bst = 'BC' and fs = 'Ab' then ist end) as bc_ab_ist
       max(case when bst = 'BC' and fs = 'Ab' then d end) as bc_ab_d,
       . . .
from t
group by zn, btg

Upvotes: 0

Related Questions