This is quite involved...
First, I use a metadata tracking system, so that the single procedure can maintain many views with a single execution. The procedure alters (or creates if the pivot view does not exist). The comments below explain it relatively well. I included your sample data in here, and can answer further questions about configuration if your needs expand.
IF OBJECT_ID('JobWeeklysCustomFields') IS NOT NULL DROP VIEW JobWeeklysCustomFields
GO
IF OBJECT_ID('Weeklys') IS NOT NULL DROP TABLE Weeklys
GO
IF OBJECT_ID('CustomFields') IS NOT NULL DROP TABLE CustomFields
GO
IF OBJECT_ID('Jobs') IS NOT NULL DROP TABLE Jobs
GO
IF OBJECT_ID('JobsCustomFields') IS NOT NULL DROP TABLE JobsCustomFields
GO
/**
JCPivot by Jaaz Cole
Covered by Creative Commons : CC BY-SA
**/
IF OBJECT_ID('JCPivot.CreatePivotViews') IS NOT NULL DROP PROCEDURE JCPivot.CreatePivotViews
GO
IF OBJECT_ID('JCPivot.UniqueTablesAggregateOrColumn') IS NOT NULL DROP VIEW JCPivot.UniqueTablesAggregateOrColumn
GO
IF OBJECT_ID('JCPivot.UniqueTablesRow') IS NOT NULL DROP VIEW JCPivot.UniqueTablesRow
GO
IF OBJECT_ID('JCPivot.TablesTo') IS NOT NULL DROP TABLE JCPivot.TablesTo
GO
IF SCHEMA_ID('JCPivot') IS NOT NULL DROP SCHEMA JCPivot
GO
CREATE SCHEMA JCPivot
GO
/*
Metadata table: distinct table and/or view names will be run
and have a special view generated for each with a _Pivot
suffix. So pivoting [dbo].[Emps] through this interface would
create [dbo].[Emps_Pivot] as defined here. RowColAgg can only
be set to 'Row', 'Col', or 'Agg'.
'Row' column names will be selected to the left of the dataset,
as normal columns. Limit 1 distinct column name per object.
'Col' Will use the distinct values in this column as column headers.
Limit 1 per object. Must have a 'Col' and an 'Agg' column
for an object to generate a pivot view.
'Agg' is the column to aggregate across the 'Col' headers, and
Also requires [AggFunction] contain only the name (no parens!)
of an aggregate function to perform for the pivot.
Limit 1 per object. Must have a 'Col' and an 'Agg' column
for an object to generate a pivot view.
*/
CREATE TABLE JCPivot.TablesTo (
schemaName SYSNAME
, objectName SYSNAME
, columnName SYSNAME
, RowColAgg VARCHAR(3) NOT NULL
, AggFunction NVARCHAR(128)
)
GO
/* Enforce no more than 1 each Aggregate and Column Header */
CREATE VIEW JCPivot.UniqueTablesAggregateOrColumn
WITH SCHEMABINDING
AS
SELECT schemaName, objectName, RowColAgg FROM JCPivot.TablesTo WHERE RowColAgg = 'Agg' OR RowColAgg = 'Col';
GO
CREATE UNIQUE CLUSTERED INDEX PK_UniqueAggOrCol ON JCPivot.UniqueTablesAggregateOrColumn(schemaName, objectName, RowColAgg);
GO
/* Enforce each Row header selected only once. */
CREATE VIEW JCPivot.UniqueTablesRow
WITH SCHEMABINDING
AS
SELECT schemaName, objectName, columnName, RowColAgg FROM JCPivot.TablesTo WHERE RowColAgg = 'Row';
GO
CREATE UNIQUE CLUSTERED INDEX PK_UniqueTablesRow ON JCPivot.UniqueTablesRow(schemaName, objectName, columnName);
GO
/* This Procedure Creates the Views */
CREATE PROCEDURE JCPivot.CreatePivotViews
AS
BEGIN
SET NOCOUNT ON
DECLARE
@SchemaName SYSNAME
, @ObjectName SYSNAME
, @ColumnHeaderColumnName SYSNAME
, @AggregateFunction SYSNAME
, @AggregateColumnName SYSNAME
DECLARE
@RowHeaderColumnNames TABLE (RowHeader SYSNAME, typeName nvarchar(128))
DECLARE ObjectCursor CURSOR FAST_FORWARD FOR
SELECT schemaName, objectName
FROM JCPivot.TablesTo
GROUP BY schemaName, objectName
HAVING SUM(CASE WHEN RowColAgg IN ('Agg','Col') THEN 1 ELSE 0 END) = 2
ORDER BY schemaName, objectName
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @SchemaName, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @RowHeaderColumnNames
INSERT INTO @RowHeaderColumnNames (RowHeader) SELECT columnName FROM JCPivot.TablesTo WHERE schemaName = @SchemaName AND objectName = @ObjectName AND RowColAgg = 'Row'
SELECT @ColumnHeaderColumnName = columnName FROM JCPivot.TablesTo WHERE schemaName = @SchemaName AND objectName = @ObjectName AND RowColAgg = 'Col'
SELECT @AggregateFunction = AggFunction, @AggregateColumnName = columnName FROM JCPivot.TablesTo WHERE schemaName = @SchemaName AND objectName = @ObjectName AND RowColAgg = 'Agg'
--Internal declarations
DECLARE @CursSQL NVARCHAR(MAX)
DECLARE @ColumnHeaderType NVARCHAR(30)
SELECT @ColumnHeaderType = C.DATA_TYPE
+ CASE
WHEN C.DATA_TYPE in ('varchar','nvarchar') THEN N'(' + cast(C.CHARACTER_MAXIMUM_LENGTH as nvarchar) + N')'
WHEN C.DATA_TYPE = 'numeric' THEN N'(' + cast(C.NUMERIC_PRECISION as nvarchar) + N',' + cast(C.NUMERIC_SCALE as nvarchar) + N')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.COLUMN_NAME = @ColumnHeaderColumnName
AND C.TABLE_SCHEMA = @SchemaName
AND C.TABLE_NAME = @ObjectName
DECLARE @RowHeaderType as nvarchar(30)
UPDATE RHCN
SET TypeName = C.DATA_TYPE
+ CASE
WHEN C.DATA_TYPE in ('varchar','nvarchar') THEN N'(' + cast(C.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + N')'
WHEN C.DATA_TYPE = 'numeric' THEN N'(' + cast(C.NUMERIC_PRECISION AS NVARCHAR(10)) + N',' + cast(C.NUMERIC_SCALE AS NVARCHAR(10)) + N')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN @RowHeaderColumnNames RHCN on C.COLUMN_NAME = RHCN.RowHeader
AND C.TABLE_SCHEMA = @SchemaName
AND C.TABLE_NAME = @ObjectName
DECLARE @AggregateColumnType SYSNAME = N''
SELECT @AggregateColumnType = C.DATA_TYPE
+ CASE
WHEN C.DATA_TYPE in ('varchar','nvarchar') THEN N'(' + cast(C.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + N')'
WHEN C.DATA_TYPE = 'numeric' THEN N'(' + cast(C.NUMERIC_PRECISION AS NVARCHAR(10)) + N',' + cast(C.NUMERIC_SCALE AS NVARCHAR(10)) + N')'
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.COLUMN_NAME = @AggregateColumnName
AND C.TABLE_SCHEMA = @SchemaName
AND C.TABLE_NAME = @ObjectName
DECLARE @RowHeaderNames NVARCHAR(MAX) = N'', @RowHeaderDeclarations NVARCHAR(MAX) = N''
SELECT
@RowHeaderNames = @RowHeaderNames + R.RowHeader + ', '
, @RowHeaderDeclarations = @RowHeaderDeclarations + R.RowHeader + ' ' + R.typeName + ', '
FROM @RowHeaderColumnNames R
SELECT
@RowHeaderNames = LEFT(@RowHeaderNames, LEN(@RowHeaderNames)-1)
, @RowHeaderDeclarations = LEFT(@RowHeaderDeclarations, LEN(@RowHeaderDeclarations)-1)
SET @CursSQL=N'
DECLARE @SQL NVARCHAR(MAX), @value ' + @ColumnHeaderType + N'
, @ColumnHeaderNames NVARCHAR(MAX) = '''', @ColumnHeaderDeclarations NVARCHAR(MAX) = ''''
SELECT
@ColumnHeaderNames = @ColumnHeaderNames + QUOTENAME(' + @ColumnHeaderColumnName + N') + '',''
, @ColumnHeaderDeclarations = @ColumnHeaderDeclarations + QUOTENAME(' + @ColumnHeaderColumnName + N') + '' ' + @AggregateColumnType + N''' + '',''
FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName) + N'
WHERE ' + @ColumnHeaderColumnName + N' IS NOT NULL
GROUP BY ' + @ColumnHeaderColumnName + N'
SELECT
@ColumnHeaderNames = LEFT(@ColumnHeaderNames, LEN(@ColumnHeaderNames)-1)
, @ColumnHeaderDeclarations = LEFT(@ColumnHeaderDeclarations, LEN(@ColumnHeaderDeclarations)-1)
SET @sql=N''
'' + CASE WHEN OBJECT_ID(''' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName + '_Pivot') + N''') IS NOT NULL THEN ''ALTER'' ELSE ''CREATE'' END + '' VIEW ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName + '_Pivot') + N' AS
SELECT ' + @RowHeaderNames + N', '' + @ColumnHeaderNames + N''
FROM (
SELECT ' + @RowHeaderNames + N',' + QUOTENAME(@AggregateColumnName) + N' AggCol,' + QUOTENAME(@ColumnHeaderColumnName) + N' HeaderCol
FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName) + N'
) dsr
PIVOT (
' + @AggregateFunction + N'(AggCol)
FOR HeaderCol IN ('' + @ColumnHeaderNames + '')) P''
EXEC sp_executesql @SQL'
EXEC sp_executesql @CursSQL
FETCH NEXT FROM ObjectCursor INTO @SchemaName, @ObjectName
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
SET NOCOUNT OFF
END
GO
/* This is Job Information for Automatic Daily upkeep of the view definitions */
/*
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'UpdatePivotViews',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'PivotViews Updated',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'UpdatePivotViews', @server_name = N'<MyServer>'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'UpdatePivotViews', @step_name=N'ExecuteScript',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC JCPivot.CreatePivotViews
',
@database_name=N'BRNOperational',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'UpdatePivotViews',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'PivotViews Updated',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'UpdatePivotViews', @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20140613,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
*/
/** BEGIN CONFIG **/
/* Your schema... */
CREATE TABLE Jobs
([JobId] int, [Name] nvarchar(50))
;
INSERT INTO Jobs
([JobId], [Name])
VALUES
(1,'Job A'),
(2,'Job B'),
(3,'Job C')
;
GO
CREATE TABLE Weeklys
([Id] int, [JobId] int, [PeriodEndDate] nvarchar(50))
;
INSERT INTO Weeklys
([Id], [JobId], [PeriodEndDate])
VALUES
(1, 1, '2013-11-24'),
(2, 1, '2013-11-24'),
(3, 2, '2013-11-24'),
(4, 2, '2013-12-22'),
(5, 3, '2013-12-22')
;
GO
CREATE TABLE CustomFields
([CustomFieldId] int, [CompanyId] int, [Name] nvarchar(50))
;
INSERT INTO CustomFields
([CustomFieldId], [CompanyId], [Name])
VALUES
(1, 1, 'Custom Field 1'),
(2, 1, 'Custom Field 2'),
(3, 2, 'Custom Field 3'),
(4, 2, 'Custom Field 4'),
(5, 3, 'Custom Field 5')
;
GO
CREATE TABLE JobsCustomFields
([JobId] int, [CustomFieldId] int, [CustomFieldValue] nvarchar(50))
;
INSERT INTO JobsCustomFields
([JobId], [CustomFieldId], [CustomFieldValue])
VALUES
(1, 1, 'Value 1'),
(1, 2, 'Value 1'),
(1, 3, 'Value 1')
;
GO
/* A new view... */
CREATE VIEW JobWeeklysCustomFields AS
SELECT w.Id, w.PeriodEndDate, CF.CompanyId, J.JobId, J.Name AS JobName, CF.Name AS CustomFieldName, JCF.CustomFieldValue
FROM Weeklys W
LEFT JOIN Jobs J on J.JobId = W.JobId
LEFT JOIN JobsCustomFields JCF ON JCF.JobId = J.JobId
LEFT JOIN CustomFields CF ON JCF.CustomFieldId = CF.CustomFieldId
GO
/* Define the Pivot Metadata... */
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'CustomFieldName', 'Col', NULL);
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'CustomFieldValue', 'Agg', 'max');
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'Id', 'Row', NULL);
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'PeriodEndDate', 'Row', NULL);
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'CompanyId', 'Row', NULL);
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'JobId', 'Row', NULL);
INSERT INTO JCPivot.TablesTo (schemaName, objectName, columnName , RowColAgg , AggFunction)
VALUES ('dbo', 'JobWeeklysCustomFields', 'JobName', 'Row', NULL);
/** END CONFIG **/
/* Run the Procedure to generate/refresh the pivot view */
EXEC JCPivot.CreatePivotViews
/* Enjoy the fruits. */
SELECT * FROM JobWeeklysCustomFields_Pivot
