'SQL Server - Reformat query output to get a list of values into one column

I'm trying to get a list of values into a specific column. I'm able to get the data, but my report needs to have a specific structure based on the Supplier Code and Operation Number (Op_No) columns. The goal is to get all "child" values from the Resource column, and create a string that will populate the Resource column where the Supplier Code is not Null; i.e., Monroe, Jackson, Dallas, Jackson. Ideally, my report would look like "2" below. Any suggestion would be greatly appreciated. Thank you!

The code below generates part "1" of the screen print.

CREATE TABLE #OPERATIONS ( Supplier_Code VARCHAR(50), Processor  
VARCHAR(25), Work_Order VARCHAR(20), Customer VARCHAR(35),
Operation_Type VARCHAR(35), Op_Code INT, Resource VARCHAR(5))

INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order,
Customer, Operation_Type, Op_Code, Resource) 
VALUES ('Monroe','102482','24023', NULL, 'SUBCONTRACT', NULL ,' ') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,'24023',NULL,'SUBCONTRACT', 250, 'RM') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES ('Jackson', '100162',NULL,'24023','SUBCONTRACT',NULL ,'') ; 
INSERT INTO  > #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,'24023',NULL,'SUBCONTRACT',400,'CH') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,'24023',  NULL,'SUBCONTRACT', 500 , 'ST') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,  '24023'NULL,'SUBCONTRACT',550 ,'AU') ; 
INSERT INTO > #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES ('Dallas','101514','24023', NULL, 'SUBCONTRACT',NULL ,' ') ;  
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES ('Jackson','100162','24023', NULL,'SUBCONTRACT',NULL ,' ') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order,
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,'24023',  NULL,'SUBCONTRACT', 650 ,'ST'); 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,NULL,  '24023',NULL,'SUBCONTRACT', 700 ,'AU'); 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,   NULL,'24023',NULL,'SUBCONTRACT' ,750,'TR') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,   NULL,'24023',NULL,'SUBCONTRACT' ,800,'EC') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,   NULL,'24023',NULL,'SUBCONTRACT' ,850,'HT') ; 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,   NULL,'24023',NULL,'SUBCONTRACT' ,900,'RW') 
INSERT INTO #OPERATIONS (Supplier_Code, Processor, Work_Order, 
Customer, Operation_Type, Op_Code, Resource) 
VALUES (NULL,   NULL,'24023',NULL,'SUBCONTRACT',950 ,'DM') ;

SELECT * FROM #OPERATIONS O

Report format



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source