SQL Pivot with varying number of rows and compound key to form columns
===

SQL pivot question with a twist, Take the following table (note year and month are seperate):

    CREATE TABLE [dbo].[tbl_BranchTargets]
        (
          [BranchID] [varchar](4) NOT NULL ,
          [Year] [smallint] NOT NULL ,
          [Month] [smallint] NOT NULL ,
          [Target] [int] NULL ,
          CONSTRAINT [PK_tbl_BranchTargets] PRIMARY KEY CLUSTERED ( [BranchID] ASC, [Year] ASC, [Month] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
        )
    ON  [PRIMARY]

and the following dummy data:

    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'001', 2012, 4, 1)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'001', 2012, 5, 117)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'001', 2012, 6, 233)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'001', 2012, 7, 386)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'003', 2012, 4, 2)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'003', 2012, 6, 234)
    INSERT [dbo].[tbl_BranchTargets] ([BranchID], [Year], [Month], [Target]) VALUES (N'003', 2012, 7, 387)

How would I model the given dummy data like this (note the year and month key columns are merged to form YYYYMM):

![Raw Data][1]

into this:

![Expected Output][2]

Note the missing entry for branch 3 in May, this needs to be handled as null. Eg, our of the 12 months, a branch might only have a target for 1 of them, so all the other months would need to be null.

I've looked into PIVOT() and clunky cursor options, but i am sturggling to find a quick best practice way of doing this, I'm assuming i need to implement some dynamic SQL + PIVOT() - but cant quite get my head round it.

I know that for dynamic pivots you first identify the column names (i think), i can do this as follows:

    DECLARE @Columns AS NVARCHAR(MAX);
    DECLARE @StrSQL AS NVARCHAR(MAX); 
     
    SET @Columns = STUFF((SELECT DISTINCT
                                ',' + QUOTENAME(CONVERT(VARCHAR, c.YEAR) + RIGHT('00' + CONVERT(VARCHAR, c.MONTH), 2))
                       FROM     tbl_BranchTargets c
        FOR           XML PATH('') ,
                          TYPE 
                ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 

But how you perform the pivot is a bit beyond me (as i am essentially merging to key columns to create the final columns) - would i need to merge the data before attempting the pivot where YYYY + MM is defined as a value in 1 column?

(I am using SQL Server 2008 R2)

  [1]: http://i.stack.imgur.com/MeVqZ.jpg
  [2]: http://i.stack.imgur.com/vOxMZ.jpg