Aggregate column based on certain conditions?-CodePudding

I guess I am trying to do a pivot table of some sort but I can't figure it out. I am trying to check each column for a missing value, then aggegrate the missing values grouping it by PatientID.

Thanks in advance.

Lab	PatientID	LabTime	Glucose	Protein	Sodium	TestMissing
1	1	10am	23	43		Sodium
2	1	11am	23		34	Protein
3	2	10am		43		Glucose
4	2	12am	23		23	Protein
5	2	1am	23	43		Sodium

Expected table

PatientID	Another header
1	Protein, Sodium
2	Glucose, Protein, Sodium

CodePudding user response：

Just to expand on my comment

Select PatientID
      ,NewValue  = string_agg(TestMissing,',')
 From YourTableOrQuery
 Group by PatientID

EDIT <=2016

Select PatientID
      ,NewValue  = stuff((Select  ','  TestMissing From cte Where PatientID=A.PatientID For XML Path ('')),1,1,'') 
 From YourTableOrQuery A
 Group by PatientID

CodePudding user response：

Just another option that will dynamically generate the MISSING value(s)

Example or dbFiddle

;with cte as ( 
Select Distinct 
       PatientID
      ,[Key]
 From  YourTable A
 Cross Apply (  Select [Key]
                From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                Where [Key] not in ('lab','patientID','LabTime')
                  and Value is null
             ) B
)
Select PatientID
      ,NewValue = string_agg([Key],',')
 From  cte
 Group By PatientID

Results

PatientID   NewValue
1           Protein,Sodium
2           Glucose,Protein,Sodium

EDIT <=2016 Version

;with cte as ( 
Select PatientID
      ,[Key]
 From  @YourTable A
 Cross Apply (  Select [Key]
                From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES  ) ) 
                Where [Key] not in ('lab','patientID','LabTime')
                  and Value is null
             ) B
)
Select PatientID 
      ,NewValue = stuff((Select  ','  [Key] From cte Where PatientID=A.PatientID For XML Path ('')),1,1,'') 
 From  cte A
 Group By PatientID