Comma separated values join to master table

Question

i want to display data when data from other table with two rows or more and to be one row with "for xml path", but alway null when use "for xml path". please look my sample table and my sql query now.

TCPAR :
IdCPAR | IdReferensi 
1        '1','2'
2        '1','3','4'

TReferensi :
IdReferensi | Referensi
1              Alfa
2              Beta 
3              Carlie
4              Delta

i want result like this:
IdCpar | IdReferensi | ReferensiName
1         '1','2'      'Alfa','Beta'

and this is my sql query :

select
a.idCpar,
a.IdReferensi,
(
    SELECT LEFT(result, Len(result) - 1) FROM(SELECT '''' + Referensi + ''',' FROM TReferensi (NOLOCK) 
    WHERE IdReferensi IN (a.IdReferensi)
    ORDER  BY Referensi FOR XML PATH ('')) x (result)
)as ReferensiName
from TCPAR as a
where a.IdCPAR=1

and the result:
idCpar | IdReferensi | ReferensiName
1          '1','2'     NULL

Please help me... thanks...

Answer 1

CREATE TABLE TReferensi
    ([IdReferensi] int, [Referensi] varchar(6))
;


INSERT INTO TReferensi
    ([IdReferensi], [Referensi])
VALUES
    (1, 'Alfa'),
    (2, 'Beta'),
    (3, 'Carlie'),
    (4, 'Delta')
;

CREATE TABLE TCPAR
    ([IdCPAR] int, [IdReferensi] varchar(100))
;

INSERT INTO TCPAR
    ([IdCPAR], [IdReferensi])
VALUES
    (1, '''1'',''2'''),
    (2, '''1'',''3'',''4''')
;



;with cte as
(
    select
        t1.IdCPAR, t2.IdReferensi
    from
    (
        select
            IdCPAR, 
            IdReferensi = cast(cast(t2.c.query('data(.)') as varchar) as int)
        from
        (
            select 
                IdCPAR,
                ReferensiXml = cast('<root><a>' + replace(REPLACE(IdReferensi,'''',''), ',', '</a><a>') + '</a></root>' as xml)
            from TCPAR
        ) t1
        cross apply ReferensiXml.nodes('/root/a') t2(c)
    ) t1
    join TReferensi t2 on t1.IdReferensi = t2.IdReferensi
)



SELECT T2.IdCPAR,T3.IdReferensi,
        STUFF((SELECT ','''+Referensi+''''
        FROM (
                select 
                    t1.IdCPAR,t2.Referensi
                from cte t1
                INNER JOIN TReferensi t2 
                ON  t2.IdReferensi = t1.IdReferensi 
             )T1
        WHERE T2.IdCPAR = T1.IdCPAR
        FOR XML PATH('')),1,1,'') AS Referensi
FROM(
    select 
        t1.IdCPAR,t2.Referensi
    from cte t1
    INNER JOIN TReferensi t2 
    ON  t2.IdReferensi = t1.IdReferensi 
 )T2
INNER JOIN TCPAR T3
 ON T3.IdCPAR = T2.IdCPAR
GROUP BY T2.IdCPAR,T3.IdReferensi

Demo