Kevin K Kevin K - 7 days ago 6
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
BEGIN TRAN
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
GROUP BY ClaimNo


ROLLBACK TRAN
Comments