Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Columbus, OH
    Posts
    69

    Unanswered: Creating one record out of multiple

    Trying to figure out easiest SQL to turn multiple records into 1 record


    TABLE A

    Name Keyword Value
    Ted,City,New York
    Ted,Job,Doctor
    Ted,Hobby,Biking

    need to convert

    Name City Job Hobby
    Ted New York Doctor Biking


    Charlie

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    with t1 (name) as (select distinct name from tablea) select t1.name,(select value from tablea as a where a.name = t1.name and a.keyword = 'City') as city,(select value from tablea as a where a.name = t1.name and a.keyword = 'Job') as Job,(select value from tablea as a where a.name = t1.name and a.keyword = 'Hobby') as hobby from t1

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This must be one of repetiting questions in many forums.

    Solutions are:
    1) Self join, if maximum number of rows to be combined was fixed.
    2) XMLAGG.
    3) Recursive query.

    Here is an answer(variation of Andy's):
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     /* Sample Data */
    table_a(Name, Keyword, Value) AS (VALUES
     ('Ted', 'City',  'New York')
    ,('Ted', 'Job',   'Doctor')
    ,('Ted', 'Hobby', 'Biking')
    ) /* End of Sample Data */
    
    SELECT a1.name || ' ' || a1.value || ' ' || a2.value || ' ' || a3.value
             AS "Combined result"
      FROM table_a a1
      LEFT JOIN
           table_a a2
       ON  a2.name = a1.name
       AND a2.keyword = 'Job'
      LEFT JOIN
           table_a a3
       ON  a3.name = a1.name
       AND a3.keyword = 'Hobby'
     WHERE a1.keyword = 'City'
    ;
    ------------------------------------------------------------------------------
    
    Combined result               
    ------------------------------
    Ted New York Doctor Biking    
    
      1 record(s) selected.

Posting Permissions

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