Thread: Creating one record out of multiple

1. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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
•