HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Merge two rows from a table in a single row

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowsmergetwosinglefromrowtable

Problem

I think it's pretty simple but I can't get around this one…
I have a table contactsFields like this :

| id | contactId | fieldType | order | value |


I want to get the email with order 0 and company with order 0 as well.

Pretty straightforward to get either one :

SELECT "contactId", "value"
FROM "contactFields"
WHERE "order" = 0 AND "fieldType" = '';


This query can return no row, or a single one because we enforce that there are never 2 fields with same fieldType, contactId and order

But I would like to get both of them at the same time (i.e. merge 2 results on a single row), like this:

| contactId | emailWithOrder0 | companyWithOrder0 |


Can I do this with a join?

Solution

@a_horse_with-no_name's query improved:

SELECT "contactId", 
        email, 
        company
FROM 
    ( SELECT "contactId", "value" AS email
      FROM "contactFields"
      WHERE "fieldType" = 'email'
        AND "order" = 0
    ) AS e
  FULL JOIN 
    ( SELECT "contactId", "value" AS company
      FROM "contactFields"
      WHERE "fieldType" = 'company'
        AND "order" = 0
    ) AS c 
  USING ("contactId") ;

Code Snippets

SELECT "contactId", 
        email, 
        company
FROM 
    ( SELECT "contactId", "value" AS email
      FROM "contactFields"
      WHERE "fieldType" = 'email'
        AND "order" = 0
    ) AS e
  FULL JOIN 
    ( SELECT "contactId", "value" AS company
      FROM "contactFields"
      WHERE "fieldType" = 'company'
        AND "order" = 0
    ) AS c 
  USING ("contactId") ;

Context

StackExchange Database Administrators Q#193544, answer score: 8

Revisions (0)

No revisions yet.