Kevin K Kevin K - 1 year ago 74
SQL Question

SQL (Access) returning multiple records as one row

ClaimNo, Diag1, Diag2, Diag3, Diag4, CPT Code
1 v912 97729
1 v912 762.3 81029
1 v912 762.3 333.9 15321
1 v912 762.3 333.9 213.0 71100

Is it possible to write a query that returns the above information as one row? For example:

ClaimNo, Diag1, Diag2, Diag3, Diag4, CPT Codes
1 v912 762.3 333.9 213.0 97729, 81029, 15321, 71100

This may be little simpler, but what about code that just captures the different CPT codes as one record:

ClaimNo, CPT Codes
1 97729, 81029, 15321, 71100

For this purpose I was hoping to see Microsoft Access syntax against Microsoft SQL Server 2012. I would be dealing with many records and much larger claim numbers. A Claim could have more or less CPT codes than the example listed.

Is it also possible to do something like this with Excel using embedded INDEX and MATCH function?

Thank you for your time.

Answer Source
CREATE TABLE #Temp(ClaimNo VARCHAR(100),Diag1 VARCHAR(100),Diag2 VARCHAR(100),Diag3 VARCHAR(100),Diag4 VARCHAR(100),CPTCode VARCHAR(100))

INSERT INTO #Temp(ClaimNo ,Diag1,Diag2,Diag3,Diag4,CPTCode)
SELECT '1','v912','','','','97729' UNION ALL
SELECT '1','v912','762.3','','','81029' UNION ALL
SELECT '1','v912','762.3','333.9','','15321' UNION ALL
SELECT '1','v912','762.3','333.9','213.0','71100' 

SELECT ClaimNo , STUFF((SELECT ',' + CPTCode FROM #Temp FOR XML PATH('')),1,1,'')
FROM #Temp

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download