How to rotate Vertical Table to Horizontal (Normal) Table

Discussion in 'MySQL' started by demaestrowin, Feb 6, 2011.

0
  1. #1
    I have table like this

    field value record
    ---------------------------
    name A 1
    address address A 1
    phone 123 1
    name B 2
    address address B 2
    phone 456 2
    name C 3
    address address C 3
    phone 789 3

    how query to rotate this table to be

    name address phone
    ----------------------------
    A address A 123
    B address B 456
    C address C 789

    thanks,
     
    demaestrowin, Feb 6, 2011 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    Your sample is a little confusing, but I think you need to restructure your table. The way you described it your table has 3 fields: 'field', 'value', 'record'. If that's right, your structure is wrong.

    Your table should have these 3 fields: 'name', 'address', and 'phone'.

    If I'm mistaken could you tell me what you get when you run this query: SELECT * FROM [yourtablenamehere];
     
    plog, Feb 7, 2011 IP
  3. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #3
    How are the values in old table related? I mean, on what basis do you identify a pair of name, address and phone in old table?
    Based on that, a query can be made to populate it into new table.
     
    mastermunj, Feb 7, 2011 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    Usually you need to make a custom script to query the old table and using login, insert the data correctly into the new table. Because the data spans multiple rows per record, I would probably not try attempting this directly within MySQL. It may be possible but a simple php or other script should be easy to create and take care of this very quickly.
     
    jestep, Feb 7, 2011 IP
  5. demaestrowin

    demaestrowin Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I continue a previous program, an old programmer make old table with field name : `field`, `value`, `record`.
    The values in old table related with value in field `record`.
    I want to create a new table with field name : `name`, `address`, `phone` from old data table.
    So i need a query [select ... from oldtable ] what the result is the same with [select `name`, `address`, `phone` from newtable ]
     
    demaestrowin, Feb 7, 2011 IP
  6. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #6
    As long as you promise to immediately create a properly structured table with this query, destroy the old table, burn the computer that the old table is on and punch the prior programmer in the throat here is the query.


    SELECT Max(If([field]='name',[value])) AS Name, Max(If([field]='phone',[value])) AS Phone, Max(If([field]='address',[value])) AS Address
FROM (SELECT tablename.record AS num
FROM tablename
GROUP BY tablename.record) AS recordnums INNER JOIN tablename ON recordnums.num = tablename.record
GROUP BY recordnums.num;
    PHP:
    For the purposes of this I am assuming your table is named 'tablename'. So whereever that appears in my query, change 'tablename' to the actual name of your table.
     
    plog, Feb 8, 2011 IP