mHelpMe mHelpMe - 1 month ago 17
SQL Question

query returning duplication's despite nested query returning no duplicates

I have the following query below which works. However it is return duplicates which I don't understand.

The first three nested queries return no duplicates so I don't understand why the end result has duplicates sedols?

;with b as
(
select sedol, wgt from myTbl
where name = 'B'
), j as
(
select sedol, wgt from myTbl
where name = 'J'
), s as
(
select sedol, wgt from myTbl
where name = 'S'
), hlds as
(
select coalesce(b.sedol, j.sedol, s.sedol) sedol, isnull(b.wgt,0) bw, isnull(j.wgt,0) jw, isnull(s.wgt,0) sw
from b full outer join j on b.sedol = j.sedol
full outer join s on b.sedol = s.sedol
)
select hlds.* from hlds
order by sedol


sample data

myTbl

sedol name wgt
abc b 1
abc j 2
abc s 3
def j 2
def s 4


current result

abc 1 2 3
def 0 2 0
def 0 0 4


should be

abc 1 2 3
def 0 2 4

Answer

Lets improve this query and save the joins with conditional aggregation:

SELECT t.sedol,
       MAX(CASE WHEN t.name = 'B' THEN t.wgt END) as [b],
       MAX(CASE WHEN t.name = 'J' THEN t.wgt END) as [j],
       MAX(CASE WHEN t.name = 'S' THEN t.wgt END) as [s]
FROM YourTable t
GROUP BY t.sedol
Comments