CODEWITHSUNDEEP
sqlsql-serverconcatenation
alwaysbelearning
alwaysbelearning

Reputation: 1

Combine values from one column for the same primary key?

I have a query that finds all medications entered into a pharmacy dispensing system on a particular day. It finds all administration times and creates a separate row for each.

PATIENT1 DRUG1  9:00AM
PATIENT1 DRUG1  1:00PM
PATIENT1 DRUG1  5:00PM
PATIENT1 DRUG1  9:00PM

I need it to combine all administration times in the same cell separated by a comma.

PATIENT1 DRUG1  9:00AM, 1:00PM, 5:00PM, 9:00PM

Upvotes: 0

Views: 424

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

for Example,In this table you hv your query result,

create table #temp (Med varchar(40),Times varchar(20))
insert into #temp values

('PATIENT1 DRUG1',  '9:00AM')
,('PATIENT1 DRUG1',  '1:00PM')
,('PATIENT1 DRUG1',  '5:00PM')
,('PATIENT1 DRUG1',  '9:00PM')
,('PATIENT1 DRUG2',  '02:00AM')

Then final query,

SELECT *
FROM
(
    SELECT Med, 
           Times
    FROM #temp
) AS src PIVOT(MAX(times) FOR times IN([00:00AM], 
                                       [01:00AM], 
                                       [02:00AM], 
                                       [9:00AM], 
                                       [1:00PM], 
                                       [5:00PM], 
                                       [9:00PM])) AS pvt;
DROP TABLE #temp;

Different timing will have diffrent Medicine.You need to write all 24 hour timing inside PIVOT.

Also time format inside PIVOT must match your result (#Temp) Time format.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Use string_agg():

select patient, drug,
       string_agg(time, ',') within group (order by time)
from t
group by patient, drug;

In older versions of SQL Server, the method uses for xml:

select pd.*,
       stuff( (select concat(',', time)
               from t
               where t.patient = pd.patient and t.drug = pd.drug
               order by time
               for xml path ('')
              ), 1, 1, ''
            ) as times
from (select distinct patient, drug) pd;

Upvotes: 2

Related Questions