How to pivot data across multiple columns
I have data in my two sample tables Customer(3columns) and CustomerAddress(10columns) as below:
Customer & Customer Address:
Desired Output :
The data should be populated in pivot format and the mapping should be like:
- If address type = “Billing”, then fill the fields starting by “Billing…”
- If address type = “Shipping”, then fill the fields starting by “Shipping…”
- If address type = “Communication”, then fill the fields starting by “Communication…”
How to achieve this output without using a cursor?
Thanks in advance
You could
1 - create a destination table or temp table and do an initial insert with one of the types (arbitrarily picking Billing, let's be alphabetical), then do multiple UPDATE statements populating the appropriate address fields WHERE addresstype = 'Communication' and UPDATE WHERE addressType = 'Shipping'. Finally, SELECT * FROM yourTable (or temp table), in the order you wanted.
or,
2 - using CTEs/derived tables, do the same sort of thing, pulling out the different addresstypes into separate result sets before joining them all on CustomerID for the final resultset (don't forget to LEFT JOIN so if they don't have one you don't lose that customer)
or
3 - use a bunch of CASE statements (I probably wouldn't do this myself, cleaner in above solutions, IMHO). SELECT CustomerID,
[BillingAddr1] = CASE WHEN addressType = 'Billing' THEN addr1 ELSE NULL END,
[BillingAddr2] = CASE WHEN addressType = 'Billing' THEN addr2 ELSE NULL END,
…
[ShippingAddr1 = CASe WHEN addressType = 'Shipping' THEN addr1 ELSE NULL END,
…
etc.
