0

I have a table in the form(result set from a select query )

rec_no f_id f_value
1 1502 text1
1 1503 text2
2 1502 text3
2 1503 text4
2 1508 text5
1 1509 text6
1 1508 text7
2 1509 text8

and i need to convert this table in the below format

recno 1502 1503 1508 1509
1 text1 text2 text7 text6
2 text3 text4 text5 text8

the values inside f_id may vary but number of recno for reach f_id would be same. How to format the first formatted data into the second form using oracle script?

MT0
  • 143,790
  • 11
  • 59
  • 117
codeseeker
  • 196
  • 1
  • 13
  • Don't pivot it in Oracle (as SQL, not just Oracle, requires statements to have a known set of columns and yours are not known). Instead, pivot it in C#. – MT0 Feb 22 '23 at 08:43

1 Answers1

0

You can do it using group by and DECODE function to pivot the rows into columns :

SELECT
    rec_no,
    MAX(DECODE(f_id, 1502, f_value)) "1502",
    MAX(DECODE(f_id, 1503, f_value)) "1503",
    MAX(DECODE(f_id, 1508, f_value)) "1508",
    MAX(DECODE(f_id, 1509, f_value)) "1509"
FROM mytable
GROUP BY rec_no
ORDER BY rec_no;

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • Thanks ,but like i said ,this f_id values are dynamic ,i cant hard code those values – codeseeker Feb 22 '23 at 08:13
  • Question is closed somehow, so i am posting query here :select * from (select f_id,recno,f_value from #temp)pivot(max(f_value) for f_id in () – codeseeker Feb 23 '23 at 06:12