CODEWITHSUNDEEP
sqlsql-server
Raven
Raven

Reputation: 859

Identifying Records Where a String Appears More Than Once

I have a following dataset that looks like:

 ID    Medication         Dose
 1          Aspirin        4
 1          Tylenol        7
 1          Aspirin        2   
 1          Ibuprofen      1
 2          Aspirin        6
 2          Aspirin        2
 2          Ibuprofen      6
 2          Tylenol        4
 3          Tylenol        3
 3          Tylenol        7
 3          Tylenol        2

I would like to develop a code that would identify patients who have been administered a medication more than once. So for example, ID 1 had Aspirin twice, ID 2 had Aspirin twice and ID 3 had Tylenol three times.

I could be wrong but I think the easiest way to do this would be to concatenate each ID based on Medication using a code similar to the one below; but I'm not quite sure what to do after that - is it possible to count if a string appears twice within a cell? 

 SELECT DISTINCT ST2.[ID],
     SUBSTRING(
         (
             SELECT ','+ST1.Medication AS [text()]
             FROM ED_NOTES_MASTER ST1
             WHERE ST1.[ID] = ST2.[ID] 
              Order BY [ID] 
        FOR XML PATH ('')
         ), 1, 200000) [Result]
    FROM ED_NOTES_MASTER  ST2

I would like the output to look like the following:

 ID MEDICATION                    Aspirin2x   Tylenol2x     Ibuprofen2x  
 1  Aspirin, Tylenol , Aspirin         YES       NO             NO
 2  Ibuprofen, Aspirin, Aspirin        YES       NO             NO           
 3  Tylenol, Tylenol ,Tylenol          NO        YES            NO

Upvotes: 1

Views: 103

Answers (1)

Rob Streeting
Rob Streeting

Reputation: 1735

For the first part of your question (identify patients that have had a particular medication more than once), you can do this using GROUP BY to group by the ID and medication, and then using COUNT to get how many times each medication was given to each patient. For example:

SELECT ID, Medication, COUNT(*) AS amount
FROM ST2
GROUP BY ID, Medication

This will give you a list of all ID - Medication combinations that appear in the table and a count of how many times each combo appears. To limit these results down to just those that are greater than 2, you can add a condition to the COUNTed field using HAVING:

SELECT ID, Medication, COUNT(*) AS amount
FROM ST2
GROUP BY ID, Medication
HAVING amount >= 2

The problem now is formatting the results in the way you want. What you will get from the query above is a list of all patient - medication combinations that came up in the table more than once, like this:

ID    |   Medication  | Count
------+---------------+-------
1     | Aspirin       | 2
2     | Aspirin       | 2
3     | Tylenol       | 3

I'd suggest that you try and work with this format if possible, because as you have found, to get multiple values returned in a comma delimited list as you have in your Medication column you have to resort to some hacks to get it to work (although a recent version of SQL Server does implement some sort of proper group concatenation functionality.). If you really need the Aspirin2x etc. columns, take a look at the PIVOT operation in SQL Server.

Upvotes: 3

Related Questions