Here's an interesting problem. Sales wanted an excel spreadsheet that would help them call Salesforce contacts that have purchased products. That's easy enough -- Contacts JOIN Assets on AccountId.
Here's the hard part: Sales wanted the products purchased as:
- 1 column per product
- Put the # of seats in the row for each contact (or blank if not purchased).

If any RDBMS this is a fairly simple task using the RDBMS specific PIVOT operation. Excel could be used to create the pivot from raw data. In either approach the solution is not simple enough (especially since the resultant spreadsheet had around 100,000 rows and 80 columns).

I solved this problem in 3 minutes with a single MS/SQL query. How? I maintain a mirror of my Salesforce production instance using the CopyStorm product.  My mirror is within 24 hours of production salesforce data (my choice -- I could have selected within 15 minutes).

Here's the query (I masked some of the data in the query).


use salesforce

SELECT * FROM
(
SELECT
ACCT.name AS accountName, CONTACT.name as ContactName
,PROD.name AS productName, ASSET.number_of_users__c AS nUsers
,ASSET.purchaseDate

FROM
Account AS ACCT
JOIN Contact AS CONTACT
ON CONTACT.accountId=ACCT.id

JOIN Asset as ASSET
ON ASSET.accountId = ACCT.id

JOIN Product2 AS PROD
ON PROD.id = ASSET.product2id
WHERE
ASSET.purchaseDate IS NOT NULL
AND PROD.family LIKE 'FamilyName%'

) AS RAWDATA
PIVOT(
SUM(nUsers) FOR productName IN ([Product A], [Product B], [Product C], [Product D])
) pvt