Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: transposing name/value to columns

    Hi,
    I have a table like the following:

    name | type | value
    ========================
    john waist 37
    john weight 240
    john height 72
    lucy waist 26
    lucy weight 140
    lucy height 64

    what I'm trying to end up with is

    name | waist | height | weight
    =============================
    john 37 72 240
    lucy 26 64 140

    I've tried two approaches, using 'case' and 'union'

    When I use case, it kinda works except a row is reserved for a single response and they are not merged into one, for example

    name | waist | height | weight
    =============================
    john 37
    john 72
    john 240


    I figured this must be one of the most common things but can't seem to find any answers online.

    Thanks

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Code:
    SELECT name, MAX(CASE WHEN type = 'waist' THEN value END) AS waist,
      MAX(CASE WHEN type = 'height' THEN value END) AS height,
      MAX(CASE WHEN type = 'weight' THEN value END) AS weight
    FROM eav_table
    GROUP BY name

Posting Permissions

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