title: PostgreSQL - Creating temporary columns instead of multiple rows
body: <p>I'm working with PostgreSQL customer records.</p>
  
  <p>My task is to export customer records.</p>
  
  <p>I have another table aside from customers with contact information.</p>
  
  <p>One contact item per line (phone, value or email, value, etc.).</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p><strong>edit -- you guys are masters</strong></p>
  
  <p>Database tables w/ columns:</p>
  
  <pre><code>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)
  }
  </code></pre>
  
  <p>So what I want to do is pull all customers with the following data:</p>
  
  <p>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</p>
  
  <p>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. </p>
  
  <p>Clear as mud, right? :-)</p>
  
  <p><strong>edit -- may have gotten it now, but would still appreciate input</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Actually, the INNER JOIN's were not what I needed... I needed to do LEFT JOIN's as described below.</strong></p>