How to pivot rows into columns to create a new table with one row per grouped value in SQL Server

I got this table:

What I want is a table of one row per Case_Number with columns of all field types and what each doctor has done in each case:

Desired output (one row per case – if 2 doctors on case so 2 rows…):

What that I’ve tried is:

SELECT
    doctor, 
    case_number,
    CASE WHEN field = 'Injection' THEN 'X' ELSE ' ' END AS INJECTION,
    CASE WHEN field = 'Surgery' THEN 'X' ELSE ' ' END AS Surgery,
    CASE WHEN field = 'H-Test' THEN 'X' ELSE ' ' END AS H-Test,
    CASE WHEN field = 'Discharge' THEN 'X' ELSE ' ' END AS Discharge
FROM
    Doctors_Table
GROUP BY
    doctor, case_number,
    CASE WHEN field = 'Injection' THEN 'X' ELSE ' ' END,
    CASE WHEN field = 'Surgery' THEN 'X' ELSE ' ' END,
    CASE WHEN field = 'H-Test' THEN 'X' ELSE ' ' END,
    CASE WHEN field = 'Discharge' THEN 'X' ELSE ' ' END

That of course didn’t work… I got this table which I don’t want:

It returned multiple rows per doctor and case…

I want 1 row per case (2 row if the case has 2 different doctors – like in table above).

Can you help me? Thank you

>Solution :

So close!
You were just missing the aggregation:

DECLARE @Cases TABLE (CaseID INT IDENTITY, Doctor NVARCHAR(50), Case_Number INT, Field NVARCHAR(50))
INSERT INTO @Cases (Doctor, Case_Number, Field) VALUES
('Brian', 2234, 'Injection'),
('Brian', 2234, 'Surgery  '),
('Flor ', 2234, 'Surgery  '),
('Flor ', 2234, 'Discharge'), 
('Brian', 1156, 'Injection'),
('Brian', 3459, 'Surgery  '),
('Flor ', 3459, 'Surgery  '),
('Brian', 3459, 'H-Test   ')

SELECT doctor, case_number,
       MAX(CASE WHEN field = 'Injection' THEN 'X' ELSE ' ' END) AS INJECTION,
       MAX(CASE WHEN field = 'Surgery' THEN 'X' ELSE ' ' END  ) AS Surgery,
       MAX(CASE WHEN field = 'H-Test' THEN 'X' ELSE ' ' END   ) AS [H-Test],
       MAX(CASE WHEN field = 'Discharge' THEN 'X' ELSE ' ' END) AS Discharge
  FROM @Cases
GROUP BY doctor, case_number

When asking questions like these it’s very helpful to provide your demo data in an easily re-usable way to allow folks to reproduce it.