asked on

rows into columns

Hi there,
I have a query that returns two rows per each license. What I need to do is to combine those two records into one.
the data looks like this:
AddressType ATTN Line1 UNIT CITY STATE POSTALCODE Country
Mailing Address ATTNTO      1234 SW 21 AVE      12      TOWNNAME      STATE      12345        US
Site Address        ATTNTO      3456 SW 31 AVE            TOWNNAME      STATE 12345        US

and I need the data like this:
Mailing AddressATTN Mailing Address Line1 Mailing AddressUNIT Mailing AddressCITY Mailing AddressSTATE Mailing AddressPOSTALCODE Mailing AddressCountry SiteAddressATTNTO SiteAddressTOWNNAME      SiteAddressSTATE ...
How can I do this?

here is the original query :

SELECT ADDRESSTYPE, ATTN, 
CASE WHEN ADDRESSTYPE = 'Mailing Address' then ADDREsSLINE1 else 
ADDRESSLINE1 + ' ' + isnull(PREDIRECTION,'') + ' ' + isnull(ADDRESSLINE2, '') + ' ' + isnull(STREETTYPE, '') END as Line1,
CASE WHEN UNITORSUITE <> '' then 'UNIT/SUITE: ' + UNITORSUITE else '' end as Unit
, CITY, [STATE], POSTALCODE, COUNTRY 
FROM BLAddresses

Open in new window

Kyle Abrahams, PMP

How do you know 2 address lines belong together?

EG: If you have more than 2 rows, which combination go together?

Essentially you could do a self join:

select ma.AddressLine1, sa.AddressLine1 from 
Bladdresses ma
left join  Bladdresses sa on ma.CustomerID = sa.CustomerID and sa.AddressType ='SiteAddress'   --for demo only, use the key
where ma.AddressType = 'Mailing Address'

Open in new window

Denis Orozco

ASKER

Good question there is a field that I didn't include it was LicenseNumber

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial