Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Row to Column

AlharthiAlharthi Member Posts: 1
Hi there,
I have the following:

SELECT '00100' SECTIONS,
1 SORT_ORDER,
' ' ||
REPLACE(
'

'
, ' ', CHR(160)) RESULTS
FROM DUAL
UNION


SELECT '00200' SECTIONS,
TO_NUMBER(TO_CHAR(AS_OF_DATE, 'YYYYMMDD')) SORT_ORDER,
REPLACE(' ' ||
TO_CHAR(AS_OF_DATE, 'MON DD') ||
LPAD(NVL(TO_CHAR(LOC_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
LPAD(NVL(TO_CHAR(YIELD_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
LPAD(NVL(TO_CHAR(LANDING_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
LPAD(NVL(TO_CHAR(DIFF_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
LPAD(NVL(TO_CHAR(FRT_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
LPAD(NVL(TO_CHAR(DIFFS_M_AVG_5_DAY, '99999.99'), ' '), 10)

, ' ', CHR(160)) || '
' RESULTS
FROM (@# CALCULATION_ID=2221.) SOURCE
WHERE SORT_ORDER_DESC <= 20
UNION
SELECT '90400' SECTIONS,
1 SORT_ORDER,
'<br style=''page-break-before:always''>' RESULTS
FROM DUAL
ORDER BY 1, 2

It gives the Answer:



Loc. Yield Landing Diff. FRT,Ins DiffS.





OCT 31.10 -2.47 .51 -2.88 1.17 -4.06

NOV 07.10 -2.13 .51 -2.54 1.17 -3.71

NOV 14.09 -2.10 .51 -2.53 1.18 -3.71

NOV 21.08 -1.86 .51 -2.29 1.18 -3.48

NOV 28.10 -2.02 .51 -2.43 1.20 -3.65



and I want to have:
OCT31 NOV07 NOV14 NOV21 NOV28

Loc. 31.10 07.10 14.09 21.08 28.10

Yield -2.47 -2.13 -2.10 -1.86 -2.02

Landing .51 .51 .51 0.51 0.51

Diff. -2.88 -2.54 -2.53 -2.29 -2.43

FRT,Ins 1.17 1.17 1.18 1.18 1.20

Diff. -4.06 -3.71 -3.71 -3.48 -3.65




Can you help please

Many thanks


Comments

  • PeteNYPeteNY Member Posts: 2
    : Hi there,
    : I have the following:
    :
    : SELECT '00100' SECTIONS,
    : 1 SORT_ORDER,
    : ' ' ||
    : REPLACE(
    : '
    :
    : '
    : , ' ', CHR(160)) RESULTS
    : FROM DUAL
    : UNION
    :
    :
    : SELECT '00200' SECTIONS,
    : TO_NUMBER(TO_CHAR(AS_OF_DATE, 'YYYYMMDD')) SORT_ORDER,
    : REPLACE(' ' ||
    : TO_CHAR(AS_OF_DATE, 'MON DD') ||
    : LPAD(NVL(TO_CHAR(LOC_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
    : LPAD(NVL(TO_CHAR(YIELD_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
    : LPAD(NVL(TO_CHAR(LANDING_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
    : LPAD(NVL(TO_CHAR(DIFF_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
    : LPAD(NVL(TO_CHAR(FRT_M_AVG_5_DAY, '99999.99'), ' '), 10) ||
    : LPAD(NVL(TO_CHAR(DIFFS_M_AVG_5_DAY, '99999.99'), ' '), 10)
    :
    : , ' ', CHR(160)) || ' ' RESULTS
    : FROM (@# CALCULATION_ID=2221.) SOURCE
    : WHERE SORT_ORDER_DESC <= 20
    : UNION
    : SELECT '90400' SECTIONS,
    : 1 SORT_ORDER,
    : '<br style=''page-break-before:always''>' RESULTS
    : FROM DUAL
    : ORDER BY 1, 2
    :
    : It gives the Answer:
    :
    :
    : Loc. Yield Landing Diff. FRT,Ins DiffS.
    :
    :
    :
    : OCT 31.10 -2.47 .51 -2.88 1.17 -4.06
    : NOV 07.10 -2.13 .51 -2.54 1.17 -3.71
    : NOV 14.09 -2.10 .51 -2.53 1.18 -3.71
    : NOV 21.08 -1.86 .51 -2.29 1.18 -3.48
    : NOV 28.10 -2.02 .51 -2.43 1.20 -3.65
    :

    : and I want to have:
    : OCT31 NOV07 NOV14 NOV21 NOV28
    : Loc. 31.10 07.10 14.09 21.08 28.10
    : Yield -2.47 -2.13 -2.10 -1.86 -2.02
    : Landing .51 .51 .51 0.51 0.51
    : Diff. -2.88 -2.54 -2.53 -2.29 -2.43
    : FRT,Ins 1.17 1.17 1.18 1.18 1.20
    : Diff. -4.06 -3.71 -3.71 -3.48 -3.65
    :
    :

    : Can you help please
    :
    : Many thanks
    :
    :
    :

    Try using a multi-dimensional array (varrays) to store the results of your query.


    VARRAY [Oct 31[31.10,-2.47,.51,.....]
    ,Nov 07[07.10,-2.13,.51,.....]
    ,Nov 14[14.09,-2.10,.51,.....]
    ,.......]

    And then pull the data out in the order you wish to display it.

    Just an idea. Let me know if you are able to make it work.


Sign In or Register to comment.