CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Thomas Scheiterbauer
Thomas Scheiterbauer

Reputation: 45

Oracle 11g SQL Combine two rows into one (with condition)

My tables look like this:

CONTACT
ID
---
1
2

CONTACTINFO
ID    |   CONTACT_ID    |   CONTACTINFOTYPE_ID    |   INFO
-------------------------------------------------------------
1     |   1             |    1                     |   'Herbert'
2     |   2             |    2                     |   'Berg'
3     |   2             |    1                     |   'Peter'
4     |   1             |    2                     |   'Gruber'
5     |   1             |    3                     |   '303-020-303'

CONTACTINFOTYPE
 ID    |   NAME
 ---------------
 1     |   'firstname'          
 2     |   'lastname'    
 3     |   'phonenumber'

This should be the result of the SQL code i try to create:

id | full name
---------------------
1  | Herbert Gruber
2  | Peter Berg

My current SQL Code looks like this 

SELECT c.id AS id, 
case when cIT.name='firstname' then cI.info end
||' '||
case when cIT.name='lastname' then cI.info end AS fullname
FROM Contact c 
JOIN ContactInfo cI ON cI.Contact_Id=c.id 
JOIN ContactInfoType cIT ON cI.ContactInfoType_id=cIT.id;

The result of this is: 

id | full name
---------------------
1  | Herbert
2  | Berg
2  | Peter
1  | Gruber

I already tried some other methods but they don't seem to work either.

Upvotes: 1

Views: 144

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

You need to GROUP BY to get matching items on the same line. The aggregate function can be MAX or MIN; I've gone with MAX here. This query will give you ID, First Name and Last Name

SELECT
  c.id AS id,
  MAX(CASE WHEN cIT.Name = 'firstname' THEN cI.Info END) AS FirstName,
  MAX(CASE WHEN cIT.Name = 'lastname' THEN cI.Info END) AS LastName
FROM Contact c
JOIN ContactInfo cI ON cI.Contact_Id=c.id
JOIN ContactInfoType cIT ON cI.ContactInfoType_id=cIT.id
GROUP BY c.id;

I can't think of a way to concatenate the first and last names in one go, but I could be overlooking something simple here. The best I can come up with is to use the above query as a subquery or a Common Table Expression (CTE). Here's the CTE approach, which I find cleaner, but use the approach that you prefer.

WITH IDInfo AS (
  SELECT
    c.id AS id,
    MAX(CASE WHEN cIT.Name = 'firstname' THEN cI.Info END) AS FirstName,
    MAX(CASE WHEN cIT.Name = 'lastname' THEN cI.Info END) AS LastName
  FROM Contact c
  JOIN ContactInfo cI ON cI.Contact_Id=c.id
  JOIN ContactInfoType cIT ON cI.ContactInfoType_id=cIT.id
  GROUP BY c.id
)
SELECT id, FirstName || ' ' || LastName AS "full name"
FROM IDInfo;

I'd strongly advise against using a God Table anti-pattern like this. As this question shows, you'll spend most of your time trying to wrestle information out of it.

Upvotes: 1

Related Questions