2

Can I create the multiple columns for multiple rows by using the PIVOT key word in Oracle by using single query instead of two queries as below. For example..

1) 

SELECT
  * 
 FROM 
(
SELECT elig.person_id,elig.cmpo_key, recm.pyin_amt FROM sp_cmpo_elig elig, sp_base_pay_recm recm 
  WHERE elig.cmpo_elig_key=recm.cmpo_elig_key AND elig.person_id='807114' ) temp
pivot (Max(temp.pyin_amt) FOR (cmpo_key) IN (1 AS pyin_amt_1, 2 AS pyin_amt_2, 3 AS pyin_amt_3, 4 AS pyin_amt_4 ))

If I use this query, I am getting an output like below..

PERSON_ID   PYIN_AMT_1   PYIN_AMT_2  PYIN_AMT_3   PYIN_AMT_4
807114       1524.23        235.25   235.25        235.25

2) 

SELECT
  * 
 FROM 
(
SELECT elig.person_id,elig.cmpo_key, recm.pyin_pct FROM sp_cmpo_elig elig, sp_base_pay_recm recm 
  WHERE elig.cmpo_elig_key=recm.cmpo_elig_key AND elig.person_id='807114' ) temp
pivot (Max(temp.pyin_pct) FOR (cmpo_key) IN (1 AS pyin_pct_1, 2 AS pyin_pct_2, 3 AS pyin_pct_3, 4 AS pyin_pct_4 ))

Output:

PERSON_ID   PYIN_PCT_1   PYIN_PCT_2  PYIN_PCT_3   PYIN_PCT_4
807114       2             5          5            3

Can I create single query instead of two queries as above. Kindly provide solutions for this.

Note : I tried with union also, if I use union it will give two records like below...

PERSON_ID   PYIN_AMT_1   PYIN_AMT_2  PYIN_AMT_3   PYIN_AMT_4
807114       1524.23        235.25   235.25        235.25
807114       2              5        5             3

my expected output not like above, I am expecting like below....

PERSON_ID   PYIN_AMT_1   PYIN_AMT_2  PYIN_AMT_3   PYIN_AMT_4 PYIN_PCT_1   PYIN_PCT_2  PYIN_PCT_3   PYIN_PCT_4
807114       1524.23        235.25   235.25        235.25        2              5        5             3
Krishna Bhaskar
  • 83
  • 1
  • 1
  • 6

2 Answers2

0

you can specify multiple aggregates in your pivot clause

try

SELECT
  * 
 FROM 
(
SELECT elig.person_id,elig.cmpo_key, recm.pyin_amt FROM sp_cmpo_elig elig, sp_base_pay_recm recm 
  WHERE elig.cmpo_elig_key=recm.cmpo_elig_key AND elig.person_id='807114' ) temp
pivot (Max(temp.pyin_amt) AMT ,Max(temp.pyin_pct) PCT FOR (cmpo_key) IN (1 AS pyin_1, 2 AS pyin_2, 3 AS pyin_3, 4 AS pyin_4 ))

You can see I have added both MAX clauses and given them aliases. I have also renames the aliases in the IN clause to remove the aggregate column name

if you use this you should en up with columns called pyin_1_amt and pyin_1_pct etc...

see also Oracle Pivot query gives columns with quotes around the column names. What? which has a full workable answer with test data.

ShoeLace
  • 3,476
  • 2
  • 30
  • 44
0
SELECT
  * 
 FROM 
(
SELECT elig.person_id,elig.cmpo_key, recm.pyin_amt FROM sp_cmpo_elig elig, sp_base_pay_recm recm 
  WHERE elig.cmpo_elig_key=recm.cmpo_elig_key AND elig.person_id='807114' ) temp
pivot (Max(temp.pyin_amt) FOR (cmpo_key) IN (1 AS pyin_amt_1, 2 AS pyin_amt_2, 3 AS pyin_amt_3, 4 AS pyin_amt_4 ))
UNION
SELECT
  * 
 FROM 
(
SELECT elig.person_id,elig.cmpo_key, recm.pyin_pct FROM sp_cmpo_elig elig, sp_base_pay_recm recm 
  WHERE elig.cmpo_elig_key=recm.cmpo_elig_key AND elig.person_id='807114' ) temp
pivot (Max(temp.pyin_pct) FOR (cmpo_key) IN (1 AS pyin_pct_1, 2 AS pyin_pct_2, 3 AS pyin_pct_3, 4 AS pyin_pct_4 ))
Jake Feasel
  • 16,785
  • 5
  • 53
  • 66