View: dbo.Check_Layout_Report_Definition_Descriptions_Per_Client_View | |||
View definition | |||
-- This allows for a common query to be shared between Scheduled_Payroll_Run_View and Scheduled_Payroll_Run_Enhanced_View -- without adding to the copy-paste maintenance burden of those two files. CREATE VIEW dbo.Check_Layout_Report_Definition_Descriptions_Per_Client_View AS WITH CheckLayoutReportDefinitionDescriptionsPerClient (client_KEY, vendor_description, payroll_description) AS ( SELECT cb.client_KEY , rd.description , rd1.description FROM dbo.Checkbook AS cb JOIN dbo.Report_Definition AS rd ON cb.vendor_layout__report_definition_KEY = rd.report_definition_KEY JOIN dbo.Report_Definition AS rd1 ON cb.payroll_layout__report_definition_KEY = rd1.report_definition_KEY WHERE cb.is_account_inactive = 0 ), ConcatinatedCheckLayoutReportDefinitionDescriptionPerClient (client_KEY, vendor_description, payroll_description) AS ( -- SELECT … FOR XML will turn the multiple rows of Report Definition descriptions into one big -- string per client(Concatinated by the delimeter ';'). -- the STUFF() function has the purpose of removing the leading delimiter from the concatinated Report Definition descriptions string. SELECT t2.client_KEY , vendor_description = STUFF((SELECT DISTINCT N'; ' + vendor_description FROM CheckLayoutReportDefinitionDescriptionsPerClient AS t1 WHERE t1.client_KEY = t2.client_KEY FOR XML PATH(N'') ), 1, 2, N'') , payroll_description = STUFF((SELECT DISTINCT N'; ' + payroll_description FROM CheckLayoutReportDefinitionDescriptionsPerClient AS t1 WHERE t1.client_KEY = t2.client_KEY FOR XML PATH(N'') ), 1, 2, N'') FROM CheckLayoutReportDefinitionDescriptionsPerClient t2 GROUP BY client_KEY ) SELECT vl.client_KEY , vl.payroll_description, vl.vendor_description FROM ConcatinatedCheckLayoutReportDefinitionDescriptionPerClient AS vl; | |||