Dear Friends, I have a query in which there is a sub query that is fetching a no of rows (single column); it may be one,two..upto six (not more than that). It is using for xml path( '' ) feature of SQL server in order to display the values as comma separated values. The query is as follows:-
select distinct (select top 1 CLAIMMASTERID from ClaimMaster where encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') ,EVC.EncounterID,EVC.CPT,(select (select ICDCode + ',' as [text()] from VisitTypeICDCPT soi where soi.VisitTypeID = t.VisitTypeID and VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6' order by VisitTypeID for xml path( '' )) from (select distinct VisitTypeID from VisitTypeICDCPT where VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') as t )DiagnosisCode,EVC.Fee,convert(varchar(10),'2010-12-14 00:00:00.000',101) from EncounterVisitCode EVC inner join VisitTypeICDCPT VTI on EVC.encounterid=VTI.encounterid where EVC.encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6'
Here the sub query(starting from :-(select (select ICDCode...)) which is fetching the data from the database and the where "Diagnosis Code" is the column name. The data query is fetching is like this:- 004.12,001.20,...upto six values (may be less). Here i want the values to be replaced by integer values depending upon the no of values being fetched by sub-query i.e., 1,2, if values in no. are two, 1,2,3, if values in no. are three and so on... I am not able to find any way out to solve this problem. If someone could help me resolve this problem i would be very grateful to him/her. Thanks Varun Sareen
modified on Friday, December 17, 2010 6:53 AM