How to write this SQL?

[gazal]

Hi Guys,

I am using Oracle 8i on Windows 2000 server, i have a table which contains a column called Ingredient_Code, its a detail table and has multiple rows, and i want to show them as three different columns for example:

Ingredient_Code
===============
971
956
900

Should be shown as

Ing_code1 Ing_code2 Ing_Code3
========= ========= =========
971 956 900

How can i do this using standard SQL syntax? please note i cant filter the records on any where clause, the rows can be N number... but the columns should be 3 only...

hope i m clear enough

Thanks in advance

Gazal

 

[gazal]

i have found the solution and here it is

SELECT record_no,
SUM(DECODE(seq,1,ingredient_code,null)) Active1,
SUM(DECODE(seq,2,ingredient_code,null)) Active2,
SUM(DECODE(seq,3,ingredient_code,null)) ACtive3
FROM
(SELECT record_no, ingredient_code,
row_number()
OVER (PARTITION BY record_no
ORDER BY record_no desc NULLS LAST) seq
FROM dcs_tb_formulation_dtl)
WHERE seq <= 3
GROUP BY RECORD_NO

HOPE MIGHT HELP OTHERS

GAZAL