Need to combine two columns into one column, and then combine multiple rows into one row from that new column.

Question

I have combined two columns into one column using CONCAT, and now need to combine multiple rows from that new column into one row. Here is my sample table:

SELECT bac.BacteriaSID

, bac.AntibioticValue

, bac.Antibiotic

, CONCAT (AntibioticValue, ' ',Antibiotic) AS ABX_sensitivity

FROM Bacteria.Antibiotic AS bac

WHERE bac.Hospital = 123

I have combined 'AntibioticValue' and 'Antibiotic' into one column (ABX_sensitivity) using the CONCAT function (also, I'm unsure if this is the correct avenue to take). It returns correctly, as noted in the image below. Now I need to combine all rows with the same "BacteriaSID" into one row. I have outlined the rows that need to be one row, according to the common BacteriaSID. How do I write this expression? (I've included the WHERE statement because I need to see how that fits into the total SQL pull). I am using SSMS.

User's image

Accepted Answer

Try this query:

select BacteriaSID, string_agg(concat(AntibioticValue, ' ', Antibiotic), ', ') as ABX_sensitivities
from Bacteria.Antibiotic
where Hospital = 123
group by BacteriaSID
order by BacteriaSID

Share via

Need to combine two columns into one column, and then combine multiple rows into one row from that new column.

0 additional answers

Your answer