Pivoting Query


I am having table like shown below

GroupName AddressType Address Notes ExpiryDate
G1 Main Add1 sss
G1 Other Add2
G1 Correspondance Add2
G1 other Add3 04/05/2016
G2 Correspondance Add34
G2 other Add12 14/06/2016

I need to have output by having address type as columns and if group having same name morethan one time in addresstype the need to concatenate 1,2.... Like other1, other2....

GroupName Main other1 other2 correspondence notes1 ExpiryDate1


Please provide:

  1. a CREATE TABLE statement
  2. an INSERT INTO statement to populate the table with test data
  3. The exact, desired output using the table and data from steps 1 and 2
  4. Your query so far. What have you got?