My Tables :


From the Above tables I want to Query Select Statements Something like this :
Version ControlTestID TestPCID
02175bd8-69b5-4171-a9b6-f0e46ce6eb05 703A,703B,704A,704B
cdff4816-9217-4dba-a608-4247964a14bc BM002,BM003,BM004
Should display like the above format...
I am using the Following Query :
DECLARE @TBL TABLE(TESTERID VARCHAR(1000),VER_CONTROL_TESTID VARCHAR(1000))
declare @ColumnNameList nvarchar(1000),@ColumnNameList1 nvarchar(1000)
SELECT @ColumnNameList = coalesce(@ColumnNameList + ',' + TEST_DESCRIPTION, TEST_DESCRIPTION),
@ColumnNameList1=VER_CONTROL_TESTID
FROM LU_TDE_VER_CONTROL_TESTERID TESTERID
Inner join LU_TDE_VER_CONTROL VERCTRL on VERCTRL.VER_CONTROL_ID = TESTERID.VER_CONTROL_TESTID
INSERT INTO @TBL
SELECT @ColumnNameList ,@ColumnNameList1
sELECT TESTTB.*,CTRL.TESTPCID,CTRL.COMPONENT,CTRL.GROUP_NAME,CTRL.VERSION_LIST FROM LU_TDE_VER_CONTROL CTRL
inner join @TBL TESTTB on ctrl.VER_CONTROL_ID = TESTTB.VER_CONTROL_TESTID
the above query is working but displaying wrong results like below.:
TestPCID VER_CONTROL_TESTID
703A,703B,704A,704B,BM002,BM003,BM004 CDFF4816-9217-4DBA-A608-4247964A14BC
After that I tried another method something like this :
SELECT
SS.VER_CONTROL_ID,
STUFF((SELECT '; ' + US.TEST_DESCRIPTION
FROM LU_TDE_VER_CONTROL_TESTERID US
WHERE US.VER_CONTROL_TESTID = SS.VER_CONTROL_ID
FOR XML PATH(''), 1, 1, '') [SECTORS/USERS]
FROM LU_TDE_VER_CONTROL SS
GROUP BY SS.VER_CONTROL_ID
it's throwing error like : Incorrect syntax near 'XML'.