For some reason, all the data are stored as property values.
The table contains 3 columns:
- card (unique identifier)
- name (property name such as “LastName”, “PimaryEmail”)
- value (the property value)
The following query transforms this layout into a normalized table as far as it covers the existing properties of my tables:
SELECT DISTINCT "card", "AllowRemoteContent"."V" AS "AllowRemoteContent","CellularNumber"."V" AS "CellularNumber","Company"."V" AS "Company","Custom1"."V" AS "Custom1","DisplayName"."V" AS "DisplayName","FaxNumber"."V" AS "FaxNumber","FirstName"."V" AS "FirstName","HomeAddress"."V" AS "HomeAddress","HomeCity"."V" AS "HomeCity","HomeCountry"."V" AS "HomeCountry","HomePhone"."V" AS "HomePhone","HomeState"."V" AS "HomeState","HomeZipCode"."V" AS "HomeZipCode","JobTitle"."V" AS "JobTitle","LastModifiedDate"."V" AS "LastModifiedDate","LastName"."V" AS "LastName","NickName"."V" AS "NickName","Notes"."V" AS "Notes","PhotoType"."V" AS "PhotoType","PhotoURI"."V" AS "PhotoURI","PopularityIndex"."V" AS "PopularityIndex","PreferDisplayName"."V" AS "PreferDisplayName","PreferMailFormat"."V" AS "PreferMailFormat","PrimaryEmail"."V" AS "PrimaryEmail","SecondEmail"."V" AS "SecondEmail","WebPage1"."V" AS "WebPage1","WorkAddress"."V" AS "WorkAddress","WorkCity"."V" AS "WorkCity","WorkCountry"."V" AS "WorkCountry","WorkPhone"."V" AS "WorkPhone","WorkState"."V" AS "WorkState","WorkZipCode"."V" AS "WorkZipCode","_JabberId"."V" AS "_JabberId","_vCard"."V" AS "_vCard" FROM "properties" AS "P"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "AllowRemoteContent") AS "AllowRemoteContent" ON "P"."card" = "AllowRemoteContent"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "CellularNumber") AS "CellularNumber" ON "P"."card" = "CellularNumber"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Company") AS "Company" ON "P"."card" = "Company"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Custom1") AS "Custom1" ON "P"."card" = "Custom1"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "DisplayName") AS "DisplayName" ON "P"."card" = "DisplayName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "FaxNumber") AS "FaxNumber" ON "P"."card" = "FaxNumber"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "FirstName") AS "FirstName" ON "P"."card" = "FirstName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeAddress") AS "HomeAddress" ON "P"."card" = "HomeAddress"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeCity") AS "HomeCity" ON "P"."card" = "HomeCity"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeCountry") AS "HomeCountry" ON "P"."card" = "HomeCountry"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomePhone") AS "HomePhone" ON "P"."card" = "HomePhone"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeState") AS "HomeState" ON "P"."card" = "HomeState"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "HomeZipCode") AS "HomeZipCode" ON "P"."card" = "HomeZipCode"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "JobTitle") AS "JobTitle" ON "P"."card" = "JobTitle"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "LastModifiedDate") AS "LastModifiedDate" ON "P"."card" = "LastModifiedDate"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "LastName") AS "LastName" ON "P"."card" = "LastName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "NickName") AS "NickName" ON "P"."card" = "NickName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "Notes") AS "Notes" ON "P"."card" = "Notes"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PhotoType") AS "PhotoType" ON "P"."card" = "PhotoType"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PhotoURI") AS "PhotoURI" ON "P"."card" = "PhotoURI"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PopularityIndex") AS "PopularityIndex" ON "P"."card" = "PopularityIndex"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PreferDisplayName") AS "PreferDisplayName" ON "P"."card" = "PreferDisplayName"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PreferMailFormat") AS "PreferMailFormat" ON "P"."card" = "PreferMailFormat"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "PrimaryEmail") AS "PrimaryEmail" ON "P"."card" = "PrimaryEmail"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "SecondEmail") AS "SecondEmail" ON "P"."card" = "SecondEmail"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WebPage1") AS "WebPage1" ON "P"."card" = "WebPage1"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkAddress") AS "WorkAddress" ON "P"."card" = "WorkAddress"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkCity") AS "WorkCity" ON "P"."card" = "WorkCity"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkCountry") AS "WorkCountry" ON "P"."card" = "WorkCountry"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkPhone") AS "WorkPhone" ON "P"."card" = "WorkPhone"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkState") AS "WorkState" ON "P"."card" = "WorkState"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "WorkZipCode") AS "WorkZipCode" ON "P"."card" = "WorkZipCode"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "_JabberId") AS "_JabberId" ON "P"."card" = "_JabberId"."C"
LEFT OUTER JOIN (SELECT "card" as C, "value" AS V FROM "properties" WHERE "name" = "_vCard") AS "_vCard" ON "P"."card" = "_vCard"."C"