Separate with comma in SqlServer

For Join Two tables
---------------------

select a.CDM_PO_HeadId,
SUBSTRING(
(
select (', '+b.CDM_Circor_PartNo) from CDM_PO_LineItem as b
where a.CDM_PO_HeadId=b.CDM_PO_HeadId
order by b.CDM_PO_HeadId,b.CDM_Circor_PartNo for XML PATH('')),2,1000
) as PartNo
from CDM_PO_LineItem as a where (a.CDM_Circor_PartNo is not null or LEN(a.CDM_Circor_PartNo)>0) group by a.CDM_PO_HeadId


For Single Table
-------------------

declare @list varchar(max)
select @list=coalesce(@list+',','')+b.CDM_Circor_PartNo from CDM_PO_HeadItem as a
inner join CDM_PO_LineItem as b
on a.CDM_PO_HeadId=b.CDM_PO_HeadId
select @list as CirPartNo