swam swam - 2 years ago 105
SQL Question

PostgreSQL - Creating temporary columns instead of multiple rows

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 {
title (company name?)
shiptoaddress_id (links to address table)
billtoaddress_id (links to address table)

address {
name (company name?)

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

party {
isdeleted [t,f]

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.

Answer Source

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.


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 ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download