SQL Server - Pivot Column and Count number of Physician Instances-CodePudding

I'm having issues with my pivot statement. Essentially the problem is we have a set of patients who have been seen by different physicians during a set time period. The patients could have seen the physician multiple times during this time period. The goal is to be able to have an output where each line represents one patient and the different physicians have their own respective column where a running count for the number of encounters that a specific patient has had with the physician is present.

My query result:

Patient_ID	Age	Sex	Race	Ethnicity	Insurance	Physician
123	60	Male	White	Not Hisp	Public	Phys123
123	60	Male	White	Not Hisp	Public	Phys123
123	60	Male	White	Not Hisp	Public	Phys123
123	60	Male	White	Not Hisp	Public	Phys456
456	40	Female	Black	Not Hisp	Private	Phys456
456	40	Female	Black	Not Hisp	Private	Phys456
789	70	Female	White	Hisp	Private	Phys789

Here's example DML and DDL as a table variable to demonstrate:

DECLARE @Visits TABLE (Patient_ID INT, Age SMALLINT, Sex NVARCHAR(10), Race NVARCHAR(10), Ethnicity NVARCHAR(10), Insurance NVARCHAR(10), Physician NVARCHAR(10))
INSERT INTO @Visits (Patient_ID, Age, Sex, Race, Ethnicity, Insurance, Physician) VALUES 
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys123'),
(123, 60, 'Male' ,'White' ,'Not Hisp' ,'Public' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(456, 40, 'Female' ,'Black' ,'Not Hisp' ,'Private' ,'Phys456'),
(789, 70, 'Female' ,'White' ,'Hisp' ,'Private' ,'Phys789')

Phys123 - Susan Marshal

Phys456 - Mike Andrews

Phys789 - Michelle Bell

What I expected:

Patient_ID	Age	Sex	Race	Ethnicity	Insurance	Susan Marshal	Mike Andrews	Michelle Bell
123	60	Male	White	Not Hisp	Public	3	1	0
456	40	Female	Black	Not Hisp	Private	0	2	0
789	70	Female	White	Hisp	Private	0	0	1

CodePudding user response：

Using the example table variable from the question, you can perform a pivot after joining to a source which contains the names. In this example I've used a values construct since there was no example data for that.

SELECT p.Patient_ID, p.Age, p.Sex, p.Race, p.Ethnicity, p.Insurance, p.[Michelle Bell], p.[Mike Andrews], p.[Susan Marshal]
  FROM (
        SELECT v.Patient_ID, v.Age, v.Sex, v.Race, v.Ethnicity, v.Insurance, A.Physician, A.Name
          FROM @Visits v
            INNER JOIN (VALUES 
                                ('Phys123', 'Susan Marshal' ),
                                ('Phys456', 'Mike Andrews'),
                                ('Phys789', 'Michelle Bell')
                               )A(Physician, Name)
              ON v.Physician = a.Physician
       ) a
    PIVOT (
           COUNT(a.Physician) FOR a.Name IN ([Susan Marshal],[Mike Andrews],[Michelle Bell])
          ) p

Patient_ID	Age	Sex	Race	Ethnicity	Insurance	Michelle Bell	Mike Andrews	Susan Marshal
123	60	Male	White	Not Hisp	Public	0	1	3
456	40	Female	Black	Not Hisp	Private	0	2	0
789	70	Female	White	Hisp	Private	1	0	0

It is worth pointing out that you must provide the values to the pivot explicitly. This can be done via dynamic SQL, but it does increase the complexity. If you have a limited number of names you need to include it's possibly better to just hard code them.