CODEWITHSUNDEEP
sql-serversubstring
chamara
chamara

Reputation: 12711

sql server substring query

I have the following sql query

select tt.id,substring((select ','+cn.DocumentTitle from ContractNewDocs cn,tblContract t where t.JobId = 8 AND t.Id = cn.ContractID order by cn.ContractID for xml path('')),2,200) as csv
FROM  tblContract tt,ContractNewDocs cn
where tt.JobId = 8 AND tt.Id = cn.ContractID
group by tt.Id

i get the following result

id     cvs
4      asss,cdddd,aaaaaaaaaaa,bbbbbbbb,cccccccccc
6      asss,cdddd,aaaaaaaaaaa,bbbbbbbb,cccccccccc

but the result i need is 

  id     cvs
  4      asss,cdddd
  6      aaaaaaaaaaa,bbbbbbbb,cccccccccc

where am i getting it wrong? can any one help me with the issue?

Upvotes: 1

Views: 599

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

select tt.id,substring((
    select ','+cn.DocumentTitle
    from ContractNewDocs cn
    where tt.Id = cn.ContractID
    order by cn.ContractID for xml path('')),2,200) as csv
FROM  tblContract tt
inner join ContractNewDocs cn on tt.Id = cn.ContractID
where tt.JobId = 8
group by tt.Id

Upvotes: 2

Related Questions