 <p>You need <code>GROUP_CONCAT/LISTAGG</code> equivalent in <code>SQL Server</code>. You can use <code>XML, STUFF and correlated subquery</code> as replacement.</p> <p>If <code>PRODUCT_ID</code> is <code>UNIQUE</code> you can use:</p> <pre><code>WITH cte AS ( SELECT PD.PRODUCT_ID, PD.PRODUCT_NAME, PD.BARCODE, PD.SUPPLIER_BARCODE, ODI.ORDER_ITEM_ID FROM PRODUCTS PD JOIN ORDER_ITEMS ODI ON PD.PRODUCT_ID = ODI.PRODUCT_ID WHERE ODI.SUPPLIER_ID = 34359738399 AND ORDER_STATUS = 6 AND ODI.RECORD_DELETED = 0 ) SELECT PRODUCT_ID, PRODUCT_NAME, BARCODE, SUPPLIER_BARCODE, [COUNTED] = COUNT(PD.PRODUCT_ID), [ORDER_ITEM_ID] = STUFF((SELECT CONCAT(',' , ORDER_ITEM_ID) FROM cte c2 WHERE c2.PRODUCT_ID = c1.PRODUCT_ID ORDER BY c2.ORDER_ITEM_ID FOR XML PATH ('')), 1, 1, '') FROM cte c1 GROUP BY PRODUCT_ID,PRODUCT_NAME,BARCODE,SUPPLIER_BARCODE HAVING COUNT(PRODUCT_ID) = 1 ORDER BY PRODUCT_ID ASC; </code></pre> <p><kbd><strong><a href="https://data.stackexchange.com/stackoverflow/query/397868" rel="nofollow"><code>LiveDemo_SimplifiedVersion</code></a></strong></kbd></p> <p>Otherwise correlate using multiple columns:</p> <pre><code>SELECT CONCAT(',' , ORDER_ITEM_ID) FROM cte c2 WHERE c2.PRODUCT_ID = c1.PRODUCT_ID AND c2.PRODUCT_NAME = c1.PRODUCT_NAME AND ... ORDER BY c2.ORDER_ITEM_ID FOR XML PATH ('')), 1, 1, '') </code></pre>