Results 1 to 3 of 3

Thread: pivot usage!

  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Question Unanswered: pivot usage!

    Hi experts.
    I wanna make a pivot but the only example that i've fount is with a nombre using DECODE. The table has got this fields:

    ID_DATO_COMUN_CON integer
    CVE_EVENTO integer
    XML_PATH varchar (300)


    ID_DATO_COMUN_CON....CVE_EVENTO.......XML_PATH
    ---------------------- -------------- ---------------
    ..............1.......................10036....... ...FIRST PATH
    ..............2.......................10036....... ...SECOND PATH
    ..............3.......................10036....... ...THITRD PATH
    ..............4.......................10036....... ...FOURTH PATH

    I need to get

    CVE_EVENTO........FIELD1.........FIELD2........... ..FIELD3...........FIELD4
    -------------- ------------ -------------- ------------- --------------
    ....10036..........FIRST PATH...SECOND PATH...THITRD PATH...FOURTH PATH

    I've tried with this:

    Code:
    SELECT
    DECODE(ID_DATO_COMUN_CON,1,xml_path) AS Q2, 
    DECODE(ID_DATO_COMUN_CON,2,xml_path) AS Q2,
    DECODE(ID_DATO_COMUN_CON,3,xml_path) AS Q3,
    DECODE(ID_DATO_COMUN_CON,4,xml_path) AS Q4
    FROM TABLE where id_dato_comun_con in (1,2,3,4) 
    and cve_evento = 10036
    And I get it:


    CVE_EVENTO........FIELD1...........FIELD2......... ...FIELD3............FIELD4
    -------------- ------------ -------------- ------------- --------------
    10036..............FIRST PATH...........NULL.............NULL.............. .NULL
    10036...................NULL........SECOND PATH........NULL...............NULL
    10036...................NULL...............NULL... ......THITRD PATH........NULL
    10036...................NULL...............NULL... ..........NULL.........FOURTH PATH


    Thanks for your help.

    Best regards!


  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ricci View Post
    Hi experts.
    I wanna make a pivot but the only example that i've fount is with a nombre using DECODE. The table has got this fields:

    ID_DATO_COMUN_CON integer
    CVE_EVENTO integer
    XML_PATH varchar (300)


    ID_DATO_COMUN_CON....CVE_EVENTO.......XML_PATH
    ---------------------- -------------- ---------------
    ..............1.......................10036....... ...FIRST PATH
    ..............2.......................10036....... ...SECOND PATH
    ..............3.......................10036....... ...THITRD PATH
    ..............4.......................10036....... ...FOURTH PATH

    I need to get

    CVE_EVENTO........FIELD1.........FIELD2........... ..FIELD3...........FIELD4
    -------------- ------------ -------------- ------------- --------------
    ....10036..........FIRST PATH...SECOND PATH...THITRD PATH...FOURTH PATH

    I've tried with this:

    Code:
    SELECT
    DECODE(ID_DATO_COMUN_CON,1,xml_path) AS Q2, 
    DECODE(ID_DATO_COMUN_CON,2,xml_path) AS Q2,
    DECODE(ID_DATO_COMUN_CON,3,xml_path) AS Q3,
    DECODE(ID_DATO_COMUN_CON,4,xml_path) AS Q4
    FROM TABLE where id_dato_comun_con in (1,2,3,4) 
    and cve_evento = 10036
    And I get it:


    CVE_EVENTO........FIELD1...........FIELD2......... ...FIELD3............FIELD4
    -------------- ------------ -------------- ------------- --------------
    10036..............FIRST PATH...........NULL.............NULL.............. .NULL
    10036...................NULL........SECOND PATH........NULL...............NULL
    10036...................NULL...............NULL... ......THITRD PATH........NULL
    10036...................NULL...............NULL... ..........NULL.........FOURTH PATH


    Thanks for your help.

    Best regards!

    Use -for example- Max to aggregate away the nulls

  3. #3
    Join Date
    Sep 2011
    Posts
    57
    I did and works ok, thanks a lot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •