I don’t want NULL and want to have values in one row

create table employee(
    empid int,
    name varchar(5),
    salarytype varchar(10),
    salary int
)

insert into employee values (1, 'A' ,'Fixed', 500);
insert into employee values (1, 'A' ,'Variable', 300);
insert into employee values (2, 'B' ,'Fixed', 500);
insert into employee values (3, 'C' ,'Fixed', 500);`

select empid,name,  Fixed = STRING_AGG(CASE When salarytype='Fixed' then salary end,' '),
       Variable = CASE When salarytype='Variable' then salary end
From employee
group by empid,name,salarytype,salary`

with this query below output I am getting where empid is coming 2 times but I want only it only 1 time`

![1]: https://i.stack.imgur.com/voYZ7.png

desired output is

![2]: https://i.stack.imgur.com/420bB.png

Answer

I think you need to fix the GROUP BY and expressions:

select empid, name,
       STRING_AGG(CASE When salarytype = 'Fixed' then salary end, ' ') as fixed,
       SUM(CASE When salarytype = 'Variable' then salary end) as variable
From employee
group by empid, name

Advertisement

Answer