Engineering Data Architecture

WITH ElectricalSettingGroup_CTE AS (
    SELECT 
        ESG.[ElectricalSettingsGroupID],
        STUFF((
            SELECT ', ' + P2.Code
            FROM [AXIS Automation].[SectionSolver].[PACAFATElectricalSettingsGroup] AS ESG2
            LEFT JOIN [FixtureSetupCodes].[ParameterAtCategoryAtFixture] AS PACAF2
                ON PACAF2.ID = ESG2.PACAF_ID
            LEFT JOIN FixtureSetupCodes.Parameters AS P2
                ON P2.ID = PACAF2.ParameterId
            WHERE ESG2.[ElectricalSettingsGroupID] = ESG.[ElectricalSettingsGroupID]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Parameters
    FROM 
        [AXIS Automation].[SectionSolver].[PACAFATElectricalSettingsGroup] AS ESG
    LEFT JOIN 
        [FixtureSetupCodes].[ParameterAtCategoryAtFixture] AS PACAF
        ON PACAF.ID = ESG.PACAF_ID
    LEFT JOIN 
        FixtureSetupCodes.Parameters AS P
        ON P.ID = PACAF.ParameterId
    GROUP BY 
        ESG.[ElectricalSettingsGroupID]
)

SELECT F.Code AS 'Fixture ID'
      ,ESG.Parameters AS PACAF
      ,SR.Name AS 'Section Rule'
	  ,SR.[MaxSectionLength] AS 'Max Section Length'
	  ,SR.[MinSectionLength] AS 'Min Section Length'
	  ,SR.[MiddleSectionLength] AS 'Middle Section Length'
	  ,SR.[AlternativeMiddleSectionLength] AS 'Alt Middle Section Length'
	  ,SR.[FirstSectionSecondChoice] AS 'First Section Second Choice'
      ,SR.[FirstSectionSecondChoiceThreshold] AS 'First Section Second Choice Threshold'
      ,SR.[IdealMiddle8ft] AS 'Ideal Middle 8ft'
      ,SR.[IdealMiddle4ft] AS 'Ideal Middle 4ft'
      ,SR.[EndCapWidth] AS 'End Cap Width'
      ,SR.[JoinerLength] AS 'Joiner Length'
      ,SR.[BlankLength] AS 'Blank Length'
      ,SR.[EndCapsWhenJoining] AS 'EndCaps When Joining'
      ,SR.[ShortenToNoDarkSpot] AS 'Shorten To No Dark Spot'
      ,SR.[LengthAsOverallLength] AS 'Length As Overall Length'
      ,EST.EqualSectionType AS 'Equal Section Type'
      ,SR.[DarkspotsBlanksInAllSections] AS 'Dark spots Blanks In All Sections'
      ,SR.[ExcludeJoinerEndcapInNL] AS 'Exclude Joiner Endcap In NL'
      ,DG.Name AS 'Dictionary Group ID' 
      ,CASE 
          WHEN [Default] = 0 THEN 'False'
          WHEN [Default] = 1 THEN 'True'
       END AS 'Default'
      ,SF.Flag AS 'Solving Flag'
      ,[FixedLength] AS 'Fixed Length'
	  ,CASE
	  WHEN [OverrideSectionRule] = 0 THEN 'False'
	  WHEN [OverrideSectionRule] = 1 THEN 'True'
	  END AS 'Override Section Rule'
  FROM [AXIS Automation].[SectionSolver].[CodeAtMechanicalSettings] AS CMS
  LEFT JOIN FixtureSetupCodes.Fixtures AS F
  ON F.id = CMS.FixtureID
  LEFT JOIN SectionSolver.SolvingFlags AS SF
  ON SF.ID = CMS.SolvingFlagID
  LEFT JOIN SectionSolver.SectionRules AS SR
  ON SR.ID = CMS.SectionRulesID
  LEFT JOIN SectionSolver.DictionaryGroup AS DG
  ON DG.ID = CMS.DictionaryGroupID
  LEFT JOIN SECTIONSOLVER.EqualSectionTypes AS EST
  ON EST.ID = SR.EqualSectionTypesID
  LEFT JOIN ElectricalSettingGroup_CTE AS ESG -- Join with the CTE
  ON CMS.PACAFGroupID = ESG.[ElectricalSettingsGroupID]