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

    Unanswered: best way to convert name/values 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
    Join Date
    Jan 2012
    Posts
    84
    Try this
    Code:
    create table x as
    select 'IPAddress' as name, '172.168.1.1' as value
    union all
    select 'Submask', '255.255.255.0'
    union all
    select 'Field1', '1'
    union all
    select 'Field2', '2';
    
    
    
    SELECT IPAddress, Submask, Field1, Field2 
    FROM (
    	select  1 x,
    		max( case when name = 'IPAddress' then name end ) IPAddress,
    		max( case when name = 'Submask' then name end ) Submask,
    		max( case when name = 'Field1' then name end ) Field1,
    		max( case when name = 'Field2' then name end ) Field2
    	from x
    	group by x
    ) x;

Posting Permissions

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