how to join two table and get best result?

A bit tricky:

;with NormalForm as (select Code, ColumnName, Debit, Credit, PK

FROM (select *, new_ID() as PK from VoucherItem) V 
UNPIVOT (Code for ColumnName IN ([GacFirstDetailAccountRef],
 [GacSecondDetailAccountRef], 
   [GacThirdDetailAccountRef])) unpvt)

cteResult as (select NF.code,  AD.Title, 
NF.ColumnName, NF.Debit, NF.Credit, 
ROW_NUMBER() OVER (PARTITION BY PK ORDER BY ColumnName) AS Rn

from NormalForm NF INNER JOIN AccountDetails AD ON NF.Code = AD.Code)

select max(case when Rn = 1 then Code end) as GacFirstDetailAccountRef, max(case when Rn=1 then Title end) as Title1,

max(case when Rn = 2 then Code end) as GacSecondDetailAccountRef, max(case when Rn=2 then Title end) as Title2,

max(case when Rn = 3 then Code end) as GacThirdDetailAccountRef, max(case when Rn=3 then Title end) as Title3,

max(debit) as debit, max(credit) as credit

from cteResult 

GROUP BY Pk

The above is from the top of my head - so may need some tweaking.

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles