Records on a single line as opposed to two lines?

Transact-SQL
1

Hi,

I hope I can explain myself.

DECLARE @test AS TABLE
(
EmpID VARCHAR,
Volume INT,
Flag INT
)
INSERT INTO @test
VALUES 
(
'E12345', '1000', '1'
)
INSERT INTO @test
VALUES 
(
'E12345', '2000', '1'
)
INSERT INTO @test
VALUES 
(
'E12345', '4000', '2'
)

SELECT * FROM @test

I want three columns from this dataset. If flag = 1 it gets summed as unit_qty, if 2 then it gets summed as unit_value.

So my desired output for above is

E12345, 3000, 4000

Everything I try with a CASE statement puts it on two lines like this

EmpID, unit_qty, unit_value
E12345, 3000, NULL
E12345, NULL, 4000

Anyone know what I'm doing wrong?

Many thanks.

2

select EmpID, sum(case when flag = 1 then Volume else 0 end) as UnitQty, sum(case when flag = 2 then Volume else 0 end) as UnitValue
FROM @test
group by EmpID

2 Likes
3

Very good, that works, thank you.