SQL Pivot with a variable number of rows and a composite key to form columns

Question

SQL query with a rotary string, take the following table (note: year and month separately):

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 simulate dummy data like this (note that the columns of the keywords of the year and month are combined in the YYYYMM form):

in it:

Note the missing entry for branch 3 in May, it must be treated as null. For example, our 12-month branch may have only one goal for 1 of them, so all other months should be zero.

I have studied PIVOT () and the awkward cursor options, but I am trying to find a quick way to do this, I assume that I need to implement some kind of dynamic SQL + PIVOT (), but I cannot hug his head.

I know that for dynamic anchor points, 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 the way you do the anchor point is a little taller than me (since I essentially merge with the key columns to create the final columns) - I would need to combine the data before trying to create a point where YYYY + MM is defined as a value in 1 column ?

(I am using SQL Server 2008 R2)

Answer 1

You were very close to the final answer. You can use PIVOT as shown below (see SQL Fiddle with Demo ):

 DECLARE @Columns AS NVARCHAR(MAX) DECLARE @StrSQL AS NVARCHAR(MAX) SET @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(4), c.YEAR) + RIGHT('00' + CONVERT(VARCHAR(2), c.MONTH), 2)) FROM tbl_BranchTargets c FOR XML PATH('') , TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') set @StrSQL = 'SELECT branchid, ' + @Columns + ' from ( select branchid , target , CONVERT(VARCHAR(4), [YEAR]) + RIGHT(''00'' + CONVERT(VARCHAR(2), [MONTH]), 2) dt from tbl_BranchTargets ) x pivot ( sum(target) for dt in (' + @Columns + ') ) p ' execute(@StrSQL) 

This will create a list of columns that you want at runtime.

Answer 2

You were close. Try the following:

 DECLARE @Columns AS NVARCHAR(MAX) DECLARE @StrSQL AS NVARCHAR(MAX) SET @Columns = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(4), c.YEAR) + RIGHT('00' + CONVERT(VARCHAR(2), c.MONTH), 2)) FROM tbl_BranchTargets c FOR XML PATH('') , TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') SET @StrSQL = ' SELECT * FROM (SELECT BranchId, CONVERT(VARCHAR(4), [YEAR]) + RIGHT(''00'' + CONVERT(VARCHAR(2), [MONTH]), 2) YearMonth, Target FROM [dbo].[tbl_BranchTargets]) T PIVOT(MIN(Target) FOR YearMonth IN (' +@Columns +')) AS PT' EXEC(@StrSQL) 

Here is the sqlfiddle to see the results.

Answer 3

You have two problems - creating periods and turning

This creates periods ...

 declare @strPeriod nvarchar(1000) select @strPeriod=N'' select @strPeriod = @strPeriod + ', ['+YearMonth +']' from ( select distinct convert(varchar(4),tbl_BranchTargets.Year) + right('0'+convert(varchar(2),tbl_BranchTargets.Month),2) as YearMonth from tbl_BranchTargets ) src select @strPeriod = substring(@strPeriod, 3,LEN(@strPeriod)) 

And it makes a pivot

 declare @sql nvarchar(4000) select @sql = N'select * from (select BranchID, convert(varchar(4),tbl_BranchTargets.Year)+right(''0''+ convert(varchar(2),tbl_BranchTargets.Month),2) as YearMonth, Target from tbl_BranchTargets) src PIVOT (sum(target) for YearMonth in (' +@strPeriod +'))p' exec (@sql)