www.blackholesurfer.com

Code: Select all

select distinct 
       r.RE_Group_Name,
       a.SourceDataset,
       r.Precedence_Weight 'Default Precedence Weight',
       r.Precedence_DeferOnNull_Flg 'DeferNullFlag',
       r.Precedence_ListCombine_Flg 'ListCombineFlag',
       r.Precedence_Explicit_Flg 'ExplicitFlag',
       r.Submitter,r.Last_Modified_By,
       case when r.[Status] = 0 then 'Active' else 'Inactive' end 'Status'
from [ARSystem].[dbo].[RE_Group] r
join RE_Dataset_MergePrecedence_Ass a on r.RE_Group_Name = a.Precedence_Group
join RE_Dataset_MergePrecedence_Set ds on a.dataset_merge_precedence_set_I = ds.datsetMergePrecedSetId
where ds.Name = 'Schwab - Merge Precedence Set'
order by 1

-- SQL for precedence full grid 

declare @columns nvarchar(max), @sql nvarchar(max)

if OBJECT_ID('tempdb..#precData') is not null
    drop table #precData

create table #precData
(
       RE_Group_Name varchar(255) null, 
       Precedence_Weight int null,
       DeferNullFlag int null,
       ListCombineFlag int null,
       ExplicitFlag int null,
       Submitter varchar(254) null,
       Last_Modified_By varchar(254) null,
       [Status] int not null,
       ClassField varchar(160) null,
       [Value] int null
)

--Grab precedence data
insert into #precData
select RE_Group_Name, Precedence_Weight,DeferNullFlag,ListCombineFlag,ExplicitFlag,Submitter,Last_Modified_By,[Status],precSet.ClassField,precSet.Value
from
       (select distinct
              r.RE_Group_Name,a.SourceDataset,r.Precedence_Weight,r.Precedence_DeferOnNull_Flg 'DeferNullFlag',r.Precedence_ListCombine_Flg 'ListCombineFlag',r.Precedence_Explicit_Flg 'ExplicitFlag',r.Submitter,r.Last_Modified_By,r.[Status]
       from [ARSystem].[dbo].[RE_Group] r
       join RE_Dataset_MergePrecedence_Ass a on r.RE_Group_Name = a.Precedence_Group
       join RE_Dataset_MergePrecedence_Set ds on a.dataset_merge_precedence_set_I = ds.datsetMergePrecedSetId
       where 
              ds.Name = 'Schwab - Merge Precedence Set') grp
join
       (select distinct 
              g.RE_Group_Name + p.Class_Name + Field_Name 'PrecName', g.RE_Group_Name as 'Group', p.Class_Name as 'Class', Field_Name as 'Field', p.Precedence_Weight as 'Value',isnull(p.Class_Name,'')+'.'+isnull(Field_Name,'') as 'ClassField'
       from RE_Precedence p 
       join RE_Group g on g.instanceId = p.RE_Group_Instance_ID
       join RE_Dataset_MergePrecedence_Ass a on g.RE_Group_Name = a.Precedence_Group
       join RE_Dataset_MergePrecedence_Set ds on a.Dataset_Merge_Precedence_Set_I = ds.datsetMergePrecedSetId
       where 
              ds.Name = 'Schwab - Merge Precedence Set') precSet on grp.RE_Group_Name = precSet.[Group]

--select * from #precData

--Create dynamic columns for pivot
set @columns = N''
select @columns+=N', p.'+quotename([Name])
from
(
    select 
              RE_Group_Name AS [Name]
    from #precData AS p
    group by RE_Group_Name
) as x

--stuff dynamic columns into dynamic sql pivot
set @sql = N'
select ClassField ''Attribute'', '+stuff(@columns, 1, 2, '')+'
from (
       select ''0-Default Precedence Weight'' ''ClassField'', Precedence_Weight, RE_Group_Name as [Name] 
    from #precData) as j 
       pivot 
       (max([Precedence_Weight]) FOR [Name] in 
          ('+stuff(replace(@columns, ', p.[', ',['), 1, 1, '')+')) AS p
union
select ClassField, '+stuff(@columns, 1, 2, '')+'
from (
       select ClassField, [value], RE_Group_Name as [Name] 
    from #precData) as j 
       pivot 
       (max([value]) FOR [Name] in 
          ('+stuff(replace(@columns, ', p.[', ',['), 1, 1, '')+')) AS p
order by 1'
exec sp_executesql @sql

if OBJECT_ID('tempdb..#precData') is not null
    drop table #precData