#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    16
    Rep Power
    0

    Best way to convert name/value to columns?


    Hi,
    I have a poorly designed database which has name/value pairs

    table1
    name | value
    ---------------------------
    IPAddress 172.12.1.2
    Submask 255.255.255.0
    ...

    I'm trying to convert it into a proper table with SQL

    table2
    IPAddress | Submask | ...

    So far the only way I found was to run subselects over and over for each string. EX

    (select value from table1 where name = 'IPAddress' ) as IPAddress,
    (select value from table1 where name = 'Submask' ) as Submask

    ...

    I've looked into case statements but ends up with blanks (one record for each attribute).

    Any help is appreciated.

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    The use of case


    CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
    END

    e.g:
    SELECT * FROM test;

    a
    ---
    1
    2
    3


    SELECT a,
    CASE WHEN a=1 THEN 'one'
    WHEN a=2 THEN 'two'
    ELSE 'other'
    END
    FROM test;

    a | case
    ---+-------
    1 | one
    2 | two
    3 | other

    I think, there is a miss concept in your problem.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2007
    Posts
    16
    Rep Power
    0
    Not sure I'm being clear. I'm NOT looking for

    a | case
    --------
    1 one
    2 two
    3 three

    Let's start with

    name | val
    -------------
    one 1
    two 2
    three 3

    I AM looking for

    one | two | three
    -------------------
    1........2......... 3

    what I get using case right now is

    one | two | three
    -------------------
    1
    ............ 2
    ...................... 3
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by dtrobert
    what I get using case right now is

    one | two | three
    -------------------
    1
    ............ 2
    ...................... 3
    Simply apply a max() on each column.

    But you might want to look into the tablefunc module. Especially into the crosstab function there:
    http://www.postgresql.org/docs/current/static/tablefunc.html

IMN logo majestic logo threadwatch logo seochat tools logo