Hello I have the following query in postgres. And I get the following result.

create temporary table "query_table" as
select
"airgoLocator_surgerytimes".iqnum as "surgery", 
adminssion_time as "Admissió in",
pre_enter_time as "Preparació in",
quiro_enter_time as "Quiròfan in",
quiro_exit_time as "Quiròfan out",
recu_enter_time as "Recuperació in",
exit_time as "Sortida",

max(case when ("airgoLocator_phase".name='Inici anestèsia') then "airgoLocator_phasehistory".timestamp else NULL end) as Inici_anestesia,
max(case when ("airgoLocator_phase".name='Inici cirurgia') then "airgoLocator_phasehistory".timestamp else NULL end) as Inici_cirurgia,
max(case when ("airgoLocator_phase".name='Fi cirurgia') then "airgoLocator_phasehistory".timestamp else NULL end) as Fi_cirurgia,
max(case when ("airgoLocator_phase".name='Fi anestèsia') then  "airgoLocator_phasehistory".timestamp else NULL end) as Fi_anestesia

from 
"airgoLocator_surgerytimes" 

inner join
"airgoLocator_surgery"
on
"airgoLocator_surgerytimes".iqnum = "airgoLocator_surgery".iqnum

inner join
"airgoLocator_phasehistory"
on
"airgoLocator_surgery".id = "airgoLocator_phasehistory".surgery_id

inner join
"airgoLocator_phase"
on
"airgoLocator_phasehistory".phase_id = "airgoLocator_phase".id

--where "airgoLocator_surgerytimes".iqnum = '0018571064'

group by "airgoLocator_surgerytimes".iqnum, "airgoLocator_surgerytimes".adminssion_time,
"airgoLocator_surgerytimes".pre_enter_time, "airgoLocator_surgerytimes".quiro_enter_time,
"airgoLocator_surgerytimes".quiro_exit_time, "airgoLocator_surgerytimes".recu_enter_time,
"airgoLocator_surgerytimes".exit_time, "airgoLocator_phase".name, "airgoLocator_phasehistory".timestamp,
"airgoLocator_surgerytimes".id, "airgoLocator_phasehistory".phase_id

order by "airgoLocator_surgerytimes".id desc, phase_id asc
;
select * from "query_table" where surgery = '0018571064';

My question is how can I get rid of the null fields and the repeated data so that I am left with a single row with all the information. For example like this:

CodePudding user response：

I can tell you a solution, but you need to know your data and check if this solution does not match with your expected result.

You can remove those columns from group by clause and wrap them with MAX Function in SELECT clause.

But if the values in different rows (except null values) are different you miss them. In this case you may decide to use SUM function. It depends on your business goal.

Because your query is very long; I provide you a simple sample to understand.

Create Table Test (
    ID int, 
    groupColumn1 int, 
    groupColumn2 int
)

INSERT Test (ID, groupColumn1, groupColumn2)
    Values (1, 10, 20),
        (1, 10, NULL),
        (1, NULL, 20)

select ID, Max(groupColumn1) groupColumn1, MAX(groupColumn2) groupColumn2
    From Test 
    Group by ID

CodePudding user response：

Due to the way you are adding the rows into your temp table, with null values, we have to filter for the columns that do not have null values. You can get the result you want in two different ways. Using either group by, or using inner join on the same table multiple times. Here is an example of both:

Using group by:

select distinct
  t1.surgury,   
  t1.Admission,
  t1.preperation,
  t1.Quirofan_in,
  t1.Quirofan_out,
  t1.recuperacion,
  t1.sortida,
  max(t1.inici_anestesia) [inici_anestesia],
  max(t1.inici_cirurgia)[inici_cirurgia],
  max(t1.fi_cirurgia)[fi_cirurgia]
from table2 t1
group by surgury, Admission, preperation, Quirofan_in, Quirofan_out, recuperacion, sortida 

Using inner join method:

select distinct
  t1.surgury,   
  t1.Admission,
  t1.preperation,
  t1.Quirofan_in,
  t1.Quirofan_out,
  t1.recuperacion,
  t1.sortida,
  t2.inici_anestesia,
  t3.inici_cirurgia,
  t4.fi_cirurgia
from table2 t1
inner join table2 t2 on t2.surgury=t1.surgury and t2.inici_anestesia is not null 
inner join table2 t3 on t3.surgury=t1.surgury and t3.inici_cirurgia is not null
inner join table2 t4 on t2.surgury=t1.surgury and t4.fi_cirurgia is not null