You don't need to PIVOT, you need to
JOIN
[
^] the tables; and I would also recommend that you use
CTE
s[
^] to make it a little cleaner.
The CTE will basically encapsulate one of your queries into a one time use alias.
I would do this for each of your queries (different name for each one)
; WITH cteA AS (
SELECT BrCode, Code, Balance = sum((ClearBal+AccumuInt+clgamt)), Overdue = sum(OverdueAmt)
FROM LON20200331
where AcSts<>9
AND ClearBal<>0
and limit=1
group by BrCode,Code
UNION ALL
SELECT BrCode, Code, Balance = sum((ClearBal+AccumuInt+clgamt)), Overdue = sum(OverdueAmt)
FROM ADV20200331
where AcSts<>9
AND ClearBal<0
and limit=1
group by BrCode,Code
)
Now should have 3 CTEs defined (I'll call them cteA, cteB, cteC), you can simply join them together.
SELECT a.Brcode, a.Code
, BalanceL1 = a.Balance, OverdueL1 = a.Overdue
, BalanceL2 = b.Balance, OverdueL2 = b.Overdue
, BalanceL3 = c.Balance, OverdueL3 = c.Overdue
FROM cteA
INNER JOIN cteB as b ON a.Brcode = b.BrCode AND a.Code = b.Code
INNER JOIN cteC as c ON a.Brcode = c.BrCode AND b.Code = c.Code
If you did all of this correctly... you should get zero results. That is because I used an
INNER JOIN
to tie these tables together; which will require that all 3 tables joined have the same [BrCode] and [code] values present.
The 3 CTEs were defined with a distinct value for
Limit so the same brcode,code combination will not be in more than 1 table.
The solution is going to be another CTE to be JOINed in. This one will be just to get the codes, and will not need the aggregate values so there will be no GROUP BY. We only want each code combo once so the
UNION
will not contain ALL.
; WITH cteM as (
SELECT Brcode,Code
FROM LON20200331
WHERE AcSts<>9
AND ClearBal<>0
AND Limit IN (1, 2, 3)
UNION
SELECT BrCode,Code
FROM ADV20200331
WHERE AcSts<>9
AND ClearBal<0
AND Limit IN (1, 2, 3)
)
The JOIN method will be switched to a
LEFT OUTER
which will only require the value in the left half of the equation. The missing values to the right will be replaced with
NULL
SELECT m.Brcode, m.Code
, BalanceL1 = a.Balance, OverdueL1 = a.Overdue
, BalanceL2 = b.Balance, OverdueL2 = b.Overdue
, BalanceL3 = c.Balance, OverdueL3 = c.Overdue
FROM cteM m
LEFT OUTER JOIN cteA a ON m.Brcode = a.BrCode AND m.Code = a.Code
LEFT OUTER JOIN cteB b ON m.Brcode = b.BrCode AND m.Code = b.Code
LEFT OUTER JOIN cteC c ON m.Brcode = c.BrCode AND m.Code = c.Code
This should get you a few results. You may need to do some work on your own, to get the results you want. I see potential flaws in this and I currently have my own work to do.
References:
Joins (SQL Server) - SQL Server | Microsoft Docs[
^]
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[
^]