July 2, 2017 at 9:48 pm
Hi there. I have a person address table where there are multiple records for a given person distinguished by address type. I need to flat-line this structure for extraction purposes and I can't make it work. Below is sample data for use.
USE [tempdb];
GO
CREATE TABLE PersonAddress
(
PersonID VARCHAR (100),
AddressType VARCHAR (100),
AddressLine1 VARCHAR (100),
AddressLine2 VARCHAR (100),
AddressLine3 VARCHAR (100),
Suburb VARCHAR (100),
State VARCHAR (100),
PostCode VARCHAR (100),
Country VARCHAR (100)
);
INSERT INTO PersonAddress
VALUES ('P100', 'HOME', '10', 'ABC St', NULL, 'Sydney', 'NSW', '2000', 'AU'),
('P100', 'WORK', 'Unit 1', '20', 'ABC St', 'Sydney', 'NSW', '2001', 'AU'),
('P200', 'HOME', '30', 'UVW St', NULL, 'Sydney', 'NSW', '2003', 'AU'),
('P300', 'WORK', 'Unit 2', '40', 'XYZ St', 'Sydney', 'NSW', '2004', 'AU');
SELECT *
FROM PersonAddress;
DROP TABLE PersonAddress;
Below is the desired output.
PersonID,HomeAddressLine1,HomeAddressLine2,HomeAddressLine3,HomeSuburb,HomeState,HomePostCode,HomeCountry,WorkAddressLine1,WorkAddressLine2,WorkAddressLine3,WorkSuburb,WorkState,WorkPostCode,WorkCountry
P100,10,ABC St,NULL,Sydney,NSW,2000,AU,Unit 1,20,ABC St,Sydney,NSW,2001,AU
P200,30,UVW St,NULL,Sydney,NSW,2002,AU,Unit 2,40,40,Sydney,NSW,2003,AU
What I tried was using a Pivot to get this to work but I could only make it work for a single field for a given type.
For example take PersonContact table with similar structure above but with a single value field.
SELECT PersonID,
[HOME] AS HomePhone,
[MOBILE] AS MobilePhone,
[BUS] AS BusinessPhone,
[FAX] AS Fax,
AS Email
FROM (SELECT PersonID,
ContactType,
ContactInfo
FROM PersonContact
WHERE EffectiveTo IS NULL) AS PersonContact_Source PIVOT (MAX (ContactInfo) FOR ContactType IN ([HOME], [MOBILE], [BUS], [FAX], )) AS PersonContact_Pivot;
Any help would be greatly appreciated.
WRACK
CodeLake
July 3, 2017 at 12:22 am
This is where the PIVOT operator becomes painful to use. CROSS-TAB's are easier to use and understand is such cases.
USE [tempdb];
GO
CREATE TABLE PersonAddress
(
PersonID VARCHAR (100),
AddressType VARCHAR (100),
AddressLine1 VARCHAR (100),
AddressLine2 VARCHAR (100),
AddressLine3 VARCHAR (100),
Suburb VARCHAR (100),
State VARCHAR (100),
PostCode VARCHAR (100),
Country VARCHAR (100)
);
INSERT INTO PersonAddress
VALUES ('P100', 'HOME', '10', 'ABC St', NULL, 'Sydney', 'NSW', '2000', 'AU'),
('P100', 'WORK', 'Unit 1', '20', 'ABC St', 'Sydney', 'NSW', '2001', 'AU'),
('P200', 'HOME', '30', 'UVW St', NULL, 'Sydney', 'NSW', '2003', 'AU'),
('P300', 'WORK', 'Unit 2', '40', 'XYZ St', 'Sydney', 'NSW', '2004', 'AU');
SELECT *
FROM PersonAddress;
SELECT PersonID,
MAX( CASE WHEN AddressType = 'HOME' THEN AddressLine1 ELSE NULL END ) AS HomeAddressLine1,
MAX( CASE WHEN AddressType = 'HOME' THEN AddressLine2 ELSE NULL END ) AS HomeAddressLine2,
MAX( CASE WHEN AddressType = 'HOME' THEN AddressLine3 ELSE NULL END ) AS HomeAddressLine3,
MAX( CASE WHEN AddressType = 'HOME' THEN Suburb ELSE NULL END ) AS HomeSuburb,
MAX( CASE WHEN AddressType = 'HOME' THEN State ELSE NULL END ) AS HomeState,
MAX( CASE WHEN AddressType = 'HOME' THEN PostCode ELSE NULL END ) AS HomePostCode,
MAX( CASE WHEN AddressType = 'HOME' THEN Country ELSE NULL END ) AS HomeCountry,
MAX( CASE WHEN AddressType = 'WORK' THEN AddressLine1 ELSE NULL END ) AS WorkAddressLine1,
MAX( CASE WHEN AddressType = 'WORK' THEN AddressLine2 ELSE NULL END ) AS WorkAddressLine2,
MAX( CASE WHEN AddressType = 'WORK' THEN AddressLine3 ELSE NULL END ) AS WorkAddressLine3,
MAX( CASE WHEN AddressType = 'WORK' THEN Suburb ELSE NULL END ) AS WorkSuburb,
MAX( CASE WHEN AddressType = 'WORK' THEN State ELSE NULL END ) AS WorkState,
MAX( CASE WHEN AddressType = 'WORK' THEN PostCode ELSE NULL END ) AS WorkPostCode,
MAX( CASE WHEN AddressType = 'WORK' THEN Country ELSE NULL END ) AS WorkCountry
FROM PersonAddress
GROUP BY PersonID;
DROP TABLE PersonAddress;
The below mentioned articles will give you a good idea about the CROSS-TAB method and its benefits over the PIVOT.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Edit: Added links to articles on the topic.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply