'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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

