PostgreSQL - Creating temporary columns instead of multiple rows

Question

I'm working with PostgreSQL customer records.

My task is to export customer records.

I have another table aside from customers with contact information.

One contact item per line (phone, value or email, value, etc.).

When I join and relate the data, I pull multiple records per customer ID (if more than 1 comm type for each customer, example phone and email).

How could I instead of making another row for each comm type, put the info into a temporary column like a phone column, a fax column, and an email column -- then have only 1 row for each customer.

edit -- you guys are masters

Database tables w/ columns:

account {
  id
  accountid
  title (company name?)
  shiptoaddress_id (links to address table)
  billtoaddress_id (links to address table)
}

address {
  city
  country
  state
  name (company name?)
  street1
  street2
  street3
  zip
  id
}

comlink {
  isdeleted
  type [Phone,Fax,E-mail,Cell,IM,FaceBook,Twitter,LinkedIn,Web Site,Other]
  value
  id
  party_id (links to party table)
}

party {
  isdeleted [t,f]
  firstname
  lastname
  prefix
  salutation
  suffix
  id
  address_id
  jobtitle_id
}

party_comlinks {
  party_id (links to party table ex: fname lname...)
  comlinks_id (links to comlink table phone, email, etc 1 item per row)
}

So what I want to do is pull all customers with the following data:

customer.id, customer.accountid, customer.title, shipping.name, shipping.street1, shipping.street2, shipping.street3, shipping.city, shipping.state, shipping.zip, billing.name, billing.street1 (etc), billing.city, billing.state, billing.zip, party.contactperson (as party.firstname + party.lastname) AND have phone, email, fax

I'm not sure if this will be possible as in the system I think you can have multiple shipping info, billing info, contact info for each customer... however where each shipping info and billing info is the same, I only want one row of phone, email, fax, etc and not multiple rows for each phone, email, fax, etc.

Clear as mud, right? :-)

edit -- may have gotten it now, but would still appreciate input

SELECT account.id, account.accountid, account.status, account.title AS "customer",
party.firstname AS "firstname", party.lastname as "lastname", address.name AS "billname",
address.street1 AS "billtostreet1", address.street2 AS "billtostreet2", address.city as "billtocity",
address.state AS "billtostate", address.zip AS "billtozip", address2.name AS "shiptoname",
address2.street1 as "shiptostreet1", address2.street2 AS "shiptostreet2", address2.city AS "shiptocity",
address2.state AS "shiptostate", address2.zip AS "shiptozip", 

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='Phone')) AS "phone",

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='Fax')) AS "fax",

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='E-Mail')) AS "email"

FROM  ( public.account account 
   INNER JOIN public.party party ON account.contact_id = party.id )  
   INNER JOIN public.comlink comlink ON party.id = comlink.party_id 
   INNER JOIN public.address address ON account.billtoaddress_id = address.id
   INNER JOIN public.contact contact ON account.contact_id = contact.id
   LEFT JOIN public.contact_shiptoaddress contact_shiptoaddress ON contact.id = contact_shiptoaddress.contact_id
   LEFT JOIN public.address address2 ON contact_shiptoaddress.shiptoaddress_id = address2.id



WHERE account.isdeleted = 'f'

--WHERE ((comlink.type = 'E-Mail')) 
    --AND ((account.walkin is null OR (NOT ( account.walkin ))))
    --AND ((NOT ( (account."status" = 'CustomerStatusInactive') )))
    --AND ((account."prospect" is null
    --OR (NOT ( account."prospect" )))) 

ORDER BY account.id ASC

Actually, the INNER JOIN's were not what I needed... I needed to do LEFT JOIN's as described below.

Can you show us an example of the data, what query you've tried, and what correct results would look like? — jmelesky
– jmelesky, Commented May 26, 2016 at 20:09
Add the definition of the tables (as create table statements). Some sample data and the expected output based on that data. Formatted text please, no screen shots — user330315
– user330315, Commented May 26, 2016 at 20:09

Peter Eisentraut · Accepted Answer · 2016-05-26 20:11:52Z

2

You'll have to do this by explicitly joining against the contact table multiple times. There is now way to have a "dynamic" set of output columns depending on the data.

Example:

SELECT customers.name, phone_contacts.value AS phone, fax_contacts.value AS fax, ...
FROM customers
  LEFT JOIN (SELECT * FROM contacts WHERE contact_type = 'phone') AS phone_contacts ON ...
  LEFT JOIN (SELECT * FROM contacts WHERE contact_type = 'fax') AS fax_contacts ON ...

answered May 26, 2016 at 20:11

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

LEFT JOIN (SELECT * FROM contacts WHERE contact_type = 'phone') can be simplified to LEFT JOIN contacts ON contact_type = 'phone' AND ...

swam Over a year ago

This is a good example, I might be able to experiment with this a bit, but not absolutely positive if it will work or not.

Collectives™ on Stack Overflow

PostgreSQL - Creating temporary columns instead of multiple rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related