2 Replies Latest reply: Jul 4, 2013 3:25 AM by lie16 RSS

    Help in Unpivoting a view

    lie16

      Hello I had a view called test2 which look like this :

      REQUEST_IDEQUIP_NOWORK_GROUPV4MSF541_TDV5MSF541_TDCREATION_DCLOSED_DDCRDCLMT_CRMT_CLMCRMCLCREATIONCLOSEDYCRYCL
      000000000067000000000141TRUKBREAKDOWNUNSCHEDULE01/09/2012 23:16:5801/12/2012 16:45:0001/09/2012 23:16:5801/12/2012 16:45:00010101012012201220122012
      000000000068000000000110TRUKBREAKDOWNUNSCHEDULE01/09/2012 23:20:4401/10/2012 05:35:1201/09/2012 23:20:4401/10/2012 05:35:12010101012012201220122012
      000000000075000000000118TRUKBREAKDOWNUNSCHEDULE01/10/2012 00:33:2701/10/2012 15:30:0001/10/2012 00:33:2701/10/2012 15:30:00010101012012201220122012
      000000000086000000000407GAMABREAKDOWNUNSCHEDULE01/10/2012 07:38:0901/10/2012 12:15:0001/10/2012 07:38:0901/10/2012 12:15:00010101012012201220122012

      and I'm about unpivoting this view using this syntax :

      select *

      from test2

      unpivot include nulls((dt, month, period) for type in ((creation_d,mt_cr,creation) as 'cr', (closed_d, mt_cl, closed) as 'cl'))

      order by request_id, period

      and I had this kind of result

      REQUEST_IDEQUIP_NOWORK_GROUPV4MSF541_TDV5MSF541_TDDCRDCLMCRMCLYCRYCLTYPEDTMONTHPERIOD
      000000000067000000000141TRUKBREAKDOWNUNSCHEDULE01/09/9180 22:15:5701/12/2012 16:45:00010120122012cr01/09/2012 23:16:58012012
      000000000067000000000141TRUKBREAKDOWNUNSCHEDULE01/09/9180 22:15:5701/12/2012 16:45:00010120122012cl01/12/2012 16:45:00012012
      000000000068000000000110TRUKBREAKDOWNUNSCHEDULE01/09/9180 22:19:4301/10/2012 05:35:12010120122012cl01/10/2012 05:35:12012012
      000000000068000000000110TRUKBREAKDOWNUNSCHEDULE01/09/9180 22:19:4301/10/2012 05:35:12010120122012cr01/09/2012 23:20:44012012
      000000000075000000000118TRUKBREAKDOWNUNSCHEDULE01/10/2012 15:30:00010120122012cr01/10/2012 00:33:27012012
      000000000075000000000118TRUKBREAKDOWNUNSCHEDULE01/10/2012 15:30:00010120122012cl01/10/2012 15:30:00012012
      000000000086000000000407GAMABREAKDOWNUNSCHEDULE01/10/9180 06:37:0801/10/2012 12:15:00010120122012cr01/10/2012 07:38:09012012
      000000000086000000000407GAMABREAKDOWNUNSCHEDULE01/10/9180 06:37:0801/10/2012 12:15:00010120122012cl01/10/2012 12:15:00012012

      This is my question, why did dcr give a different result,and any solution to fix this?

      PS : Creating new table is not allowed, I'm using Oracle 10G