patternsqlMinor
Merge two rows from a table in a single row
Viewed 0 times
rowsmergetwosinglefromrowtable
Problem
I think it's pretty simple but I can't get around this one…
I have a table
I want to get the
Pretty straightforward to get either one :
This query can return no row, or a single one because we enforce that there are never 2 fields with same
But I would like to get both of them at the same time (i.e. merge 2 results on a single row), like this:
Can I do this with a join?
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 orderBut 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.