I need to consolidate data from our SQL 2005 database to make it easier to create a catalog. Currently the data is stored in a table named Matrix_Items as show in the picture Matrix_Items. I need to be able to get the data into a columns labeled Matrix_Group_Code and Matrix_Item_Code in a table named GRS_Catalog. I need to have the data in the below format.
1147 11470-0001, 11471-0001, 1172-0001, 11473-0001, 1147cle
1148 11480-0001, 11481-0001, 1182-0001, 11483-0001, 11484-0001
I have been doing research online and I have been able to create a code that gets the data in the correct format when I execute it in a SQL Query but I don't know how to get it to insert into the table, I have attached the code that I have created. Any help would be greatly appreciated.
( SELECT Item_Code + ','
FROM Everest_Everest.dbo.MATRIX_ITEMS p2
WHERE p2.Matrix_Group_Code = p1.Matrix_Group_Code
ORDER BY Matrix_Group_Code
FOR XML PATH('') ) AS Matrix_Item_Code
FROM Everest_Everest.dbo.MATRIX_ITEMS p1
GROUP BY Matrix_Group_Code;