I’m attempting to build code to create an SSRS report. The folder type ID 19 indicates a Member ID. If the Folder type ID includes a *1300 it indicates a provider ID that is located in that field, prior to the *1300. The issue with how I am currently doing it is that it is creating 2 rows. One where the Provider ID is populated but the Member ID is showing as NULL. The other where the Member ID is populated but the Provider ID is showing as NULL. What I want to do is combine them so I’m only returning 1 row, with both the Member ID and the Provider ID populated correctly. What is the best way to go about this? Thanks in advance for any assistance!
SELECTxfl.xml_file_id , dh.document_id , dh.document_description , dh.document_date , CASE WHEN xfi.FOLDER_TYPE_ID = '19' THEN xfi.folder_id ELSE null END AS MemberID, CASE WHEN RIGHT(xfi.folder_id, 5) = '*1300' THEN LEFT(xfi.folder_id, LEN(xfi.folder_id) - 5) ELSE null END AS ProviderID, xfl.xml_file_name , dp.document_file_path FROM reporting.[FacetsRunout].[XML_FILE_LIST] xfl JOIN reporting.[FacetsRunout].[Folder_ID] xfi ON xfi.XML_FILE_ID = XFL.XML_FILE_IDJOIN reporting.[FacetsRunout].[Document_Page] dp ON dp.XML_FILE_ID = xfl.XML_FILE_IDJOIN reporting.[FacetsRunout].[Document_Header] dh ON dh.XML_FILE_ID = xfl.XML_FILE_IDWHEREdh.DOCUMENT_ID = '07330C906700'Advertisement
Answer
You can group by all the columns that you select and aggregate on the columns that you want to combine into 1 row:
SELECTxfl.xml_file_id , dh.document_id , dh.document_description , dh.document_date , MAX(CASE WHEN xfi.FOLDER_TYPE_ID = '19' THEN xfi.folder_id END) AS MemberID, MAX(CASE WHEN RIGHT(xfi.folder_id, 5) = '*1300' THEN LEFT(xfi.folder_id,LEN(xfi.folder_id) - 5) END) AS ProviderID, xfl.xml_file_name , dp.document_file_path FROM reporting.[FacetsRunout].[XML_FILE_LIST] xfl JOIN reporting.[FacetsRunout].[Folder_ID] xfi ON xfi.XML_FILE_ID = XFL.XML_FILE_IDJOIN reporting.[FacetsRunout].[Document_Page] dp ON dp.XML_FILE_ID = xfl.XML_FILE_IDJOIN reporting.[FacetsRunout].[Document_Header] dh ON dh.XML_FILE_ID = xfl.XML_FILE_IDWHERE dh.DOCUMENT_ID = '07330C906700'GROUP BY xfl.xml_file_id , dh.document_id , dh.document_description , dh.document_date , xfl.xml_file_name , dp.document_file_pathI also removed ELSE null from both CASE statements because it is redundant.