0

My Tables :

enter image description here

enter image description here

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'.

1 Answer 1

2

You're missing a ) after FOR XML PATH('')

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 
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.