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