SQL Server - select column using in having count()

Question

This is my first question (and sorry for my English)

I have this table in SQL Server:

id_patient      | date          | id_drug
----------------------------------------------------
1                 20200101        A
1                 20200102        A
1                 20200103        A
1                 20200104        A
1                 20200105        A
1                 20200110        A
2                 20200101        A
2                 20200105        B
2                 20200106        C
2                 20200107        D
2                 20200108        E
2                 20200110        L
3                 20200101        A
3                 20200102        A
3                 20200103        A
3                 20200104        A
3                 20200105        C
3                 20200106        C
4                 20200105        A
4                 20200106        D
4                 20200107        D
5                 20200105        A
5                 20200106        A
5                 20200107        C
5                 20200108        D

I would like to extract patient and drug for all patients who have taken at least 3 different drugs in a given period

I have tried:

select id_patient, count(distinct ID_drug)
from table
where date between XXX and YYY
group by id_patient
having count(Distinct ID_drug) > 3

but in this way -YES- I get all patients with 3 or more different id_drug in this date range but I can't get the ID_drug because in the count()

For example, I'd like to obtain:

Who help me ? Thanks

Answer 1

You can use string_agg() in the most recent versions of SQL Server:

select id_patient, count(distinct ID_drug),
       string_agg(id_drug, ',')
from table
where date between XXX and YYY
group by id_patient
having count(Distinct ID_drug) > 3;

If you want the original rows, you can use window functions. Unfortunately, SQL Server does not support count(distinct) as a window function, but there is an easy work-around using dense_rank():

select t.*
from (select t.*,
             (dense_rank() over (partition by id_patient order by id_drug) +
              dense_rank() over (partition by id_patient order by id_drug desc)
             ) as num_drugs
      from t
      where . . . 
     ) t
where num_drugs >= 3;

Answer 2

SELECT          id_patient, 
                ID_drug
FROM            table
WHERE           id_patient IN (
  SELECT        id_patient
  FROM          table
  WHERE         date 
    BETWEEN     XXX 
    AND         YYY
  GROUP BY      id_patient
  HAVING        COUNT(DISTINCT ID_drug) >= 3
)
GROUP BY        id_patient, 
                ID_drug;