Professional Web Applications Themes

How to do this Oracle trick (crosstab) in mysql? - MySQL

Hi, Often you have data of this form: Name,itemnr,value Peter,1,10 Peter,2,20 Peter,3,20 John,1,15 John,2,40 John,3,50 To spare place i want it this way: Name,itemnr1,itemnr2,itemnr3 Peter,10,20,20 John,15,40,50 In Oracle i can do this: select name, decode(itemnr,1,value) as itemnr1, decode(itemnr,2,value) as itemnr2, decode(itemnr,3,value) as itemnr3 from ... and to get it all in a single row per person: select name, decode(itemnr,1,value) itemnr1, decode(itemnr,2,value) itemnr2, decode(itemnr,3,value) itemnr3 from ... group by name; For the decode part in mysql i can use: select name, if (itemnr=1, value) as itemnr1, if (itemnr=2,value) as itemnr2, if (itemnr=3,value) as itemnr3 from ... group by name But this ...

  1. #1

    Default How to do this Oracle trick (crosstab) in mysql?

    Hi,

    Often you have data of this form:

    Name,itemnr,value
    Peter,1,10
    Peter,2,20
    Peter,3,20
    John,1,15
    John,2,40
    John,3,50

    To spare place i want it this way:

    Name,itemnr1,itemnr2,itemnr3
    Peter,10,20,20
    John,15,40,50

    In Oracle i can do this:

    select
    name,
    decode(itemnr,1,value) as itemnr1,
    decode(itemnr,2,value) as itemnr2,
    decode(itemnr,3,value) as itemnr3
    from ...

    and to get it all in a single row per person:
    select
    name,
    decode(itemnr,1,value) itemnr1,
    decode(itemnr,2,value) itemnr2,
    decode(itemnr,3,value) itemnr3
    from ...
    group by name;

    For the decode part in mysql i can use:

    select name,
    if (itemnr=1, value) as itemnr1,
    if (itemnr=2,value) as itemnr2,
    if (itemnr=3,value) as itemnr3
    from ...
    group by name

    But this does not work.

    Question: How do you do a crosstab conversion in mysql in an elegant
    easy way?

    thanks in advance,

    Toni

    --
    Posen fuer Anfaenger: http://www.w-klch.med.uni-muenchen.de/dischner
    A. Guest

  2. #2

    Default Re: How to do this Oracle trick (crosstab) in mysql?

    Hi,


    did some more testing:

    select name,
    sum(if (itemnr=1,value)) as itemnr1,
    sum(if (itemnr=2,value)) as itemnr2,
    sum(if (itemnr=3,value)) as itemnr3
    from ...
    group by name

    seems to do the trick.

    Right?

    Any more suggestions hints?

    Toni

    --
    Posen fuer Anfaenger: http://www.w-klch.med.uni-muenchen.de/dischner
    A. Guest

  3. #3

    Default Re: How to do this Oracle trick (crosstab) in mysql?

    On Feb 28, 12:46 pm, "A. Dischner" <uni-
    muenchen.de> wrote: 

    have a look at GROUP_CONCAT

    strawberry Guest

Similar Threads

  1. connnecting to both mysql and oracle
    By shavian78@gmail.com in forum MySQL
    Replies: 0
    Last Post: September 18th, 03:59 PM
  2. how can i migrate data from oracle to mysql?
    By news.yaako.com in forum MySQL
    Replies: 2
    Last Post: January 13th, 08:27 AM
  3. PostgreSQL vs. MySQL vs. Oracle, 2005 report card
    By Shridhar in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: February 17th, 12:43 AM
  4. No true Crosstab Query in Oracle???
    By damorgan in forum Oracle Server
    Replies: 0
    Last Post: December 30th, 05:48 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139