• Here is a quick demonstration of a dynamic SQL solution, in case there is no option of changing/adding any tables in the database.

    😎

    USE tempdb;

    GO

    /* TEST DATA */

    CREATE TABLE dbo.Planner(

    [Name] [nvarchar](255) NULL

    ,[ProjectName] [nvarchar](255) NULL

    ,[7-Jul-14][nvarchar](255) NULL

    ,[14-Jul-14][nvarchar](255) NULL

    ,[21-Jul-14][nvarchar](255) NULL

    ,[28-Jul-14][nvarchar](255) NULL

    ,[4-Aug-14][nvarchar](255) NULL

    ,[11-Aug-14][nvarchar](255) NULL

    ,[18-Aug-14][nvarchar](255) NULL

    ,[25-Aug-14][nvarchar](255) NULL

    ,[1-Sep-14][nvarchar](255) NULL

    ,[8-Sep-14][nvarchar](255) NULL

    ,[15-Sep-14][nvarchar](255) NULL)

    insert into Planner (Name,ProjectName,[7-Jul-14]

    ,[14-Jul-14]

    ,[21-Jul-14]

    ,[28-Jul-14]

    ,[4-Aug-14]

    ,[11-Aug-14]

    ,[18-Aug-14]

    ,[25-Aug-14]

    ,[1-Sep-14]

    ,[8-Sep-14]

    ,[15-Sep-14]) Values ('Mike','Audit','10','10','10','0','5','5','5','4','15','14','10')

    DECLARE @TABLE_NAME NVARCHAR(128) = N'Planner'

    DECLARE @TABLE_SCHEMA NVARCHAR(128) = N'dbo'

    DECLARE @PARAM NVARCHAR(128) = N'@TABLE_NAME NVARCHAR(128),@TABLE_SCHEMA NVARCHAR(128)';

    DECLARE @NORMALIZED_DATA TABLE

    (

    ND_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    ,P_NAME NVARCHAR(50) NOT NULL

    ,P_PROJECT_NAME NVARCHAR(100) NOT NULL

    ,DATE_VAL DATE NOT NULL

    ,P_VALUE INT NOT NULL

    );

    DECLARE @SQL_STR NVARCHAR(MAX) = N'';

    SELECT @SQL_STR = N'SELECT

    P.Name

    ,P.ProjectName

    ,CONVERT(DATE,C.COLUMN_NAME,105) AS DATE_VAL

    ,CASE

    '+ (

    SELECT

    N'WHEN C.COLUMN_NAME = N' + NCHAR(39) + C.COLUMN_NAME + NCHAR(39) + N' THEN P.[' + C.COLUMN_NAME + N']

    '

    FROM INFORMATION_SCHEMA.COLUMNS C

    WHERE C.TABLE_NAME = N'Planner'

    AND C.TABLE_SCHEMA = N'dbo'

    AND C.ORDINAL_POSITION > 2

    FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)') + N' END AS COL_VAL

    FROM INFORMATION_SCHEMA.COLUMNS C

    OUTER APPLY dbo.Planner P

    WHERE C.TABLE_NAME = @TABLE_NAME

    AND C.TABLE_SCHEMA = @TABLE_SCHEMA

    AND C.ORDINAL_POSITION > 2';

    /*

    PRINT @SQL_STR

    */

    INSERT INTO @NORMALIZED_DATA (P_NAME,P_PROJECT_NAME,DATE_VAL,P_VALUE)

    EXEC SP_EXECUTESQL @SQL_STR, @PARAM, @TABLE_NAME, @TABLE_SCHEMA;

    DECLARE @TODAY DATE = '2014-07-23';

    DECLARE @PER_LEN INT = 14;

    DECLARE @FORTNIGHT DATE = DATEADD(DAY,14,@TODAY);

    SELECT

    ND.P_NAME

    ,ND.P_PROJECT_NAME

    ,@TODAY AS TODAY

    ,SUM(ND.P_VALUE) AS SUM_PERIOD

    FROM @NORMALIZED_DATA ND

    WHERE ND.DATE_VAL BETWEEN @TODAY AND @FORTNIGHT

    GROUP BY ND.P_NAME,ND.P_PROJECT_NAME

    /* CLEAN UP */

    DROP TABLE dbo.Planner;

    Results

    P_NAME P_PROJECT_NAME TODAY SUM_PERIOD

    ------- --------------- ---------- -----------

    Mike Audit 2014-07-23 5