question

swarnava2014 avatar image
swarnava2014 asked

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

pivot
customeraddress.png (30.0 KiB)
mergeddata.png (22.0 KiB)
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

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.

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.