Replace with integer (1,2,3...) Problem in SQL Query [modified]

Varun Sareen

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

Hiren solanki · modified on Friday, December 17, 2010 6:53 AM

Please wrap your query in <pre lang="sql"><pre> TAG. so that we can see something from that mess.

Regards, Hiren. Microsoft Dynamics CRM

My Recent Article: - Way to know which control have raised PostBack[^]

Corporal Agarn

I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.

Jorgen Andersson

djj55 wrote:

I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.

Agreed! But reformatting takes 10 seconds, so if someone else wants to give it a shot:

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'

"When did ignorance become a point of view" - Dilbert

Varun Sareen

Dear djj55, I have formatted the query. Please go through it and if you have any answer to it then kindly let me know. Thanks & Regards Varun Sareen

Hiren solanki

I think you haven't applied lang="sql" Yet. It's not indented.

Regards, Hiren.

My Recent Article: - Way to know which control have raised PostBack
My Recent Tip/Trick: - Remove HTML Tag, get plain Text

Varun Sareen

Dear Hiren, I have made the changes as required. Kindly provide me with some answer, if you have. Thanks Varun Sareen

Hiren solanki

You can use ROW_NUMBER a Inbuilt ranking function in SQL to get only row numbers. See MSDN[^].

Regards, Hiren.

My Recent Article: - Way to know which control have raised PostBack
My Recent Tip/Trick: - Remove HTML Tag, get plain Text

Corporal Agarn

As stated before: "I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries." Sorry