Click here to Skip to main content
15,882,329 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have below mentioned three queries, I want to combine their ouputs side by side.
I have no idea how to do that please help...!!

SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM LON20200331 where AcSts<>9 AND ClearBal<>0 and limit=1 group by BrCode,Code
UNION ALL
SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM ADV20200331 where AcSts<>9 AND ClearBal<0 and limit=1 group by BrCode,Code


SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM LON20200331 where AcSts<>9 AND ClearBal<>0 and limit=2 group by BrCode,Code
UNION ALL
SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM ADV20200331 where AcSts<>9 AND ClearBal<0 and limit=2 group by BrCode,Code


SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM LON20200331 where AcSts<>9 AND ClearBal<>0 and limit=3 group by BrCode,Code
UNION ALL
SELECT BrCode,Code,sum((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) FROM ADV20200331 where AcSts<>9 AND ClearBal<0 and limit=3 group by BrCode,Code


Output should be like this.

HTML
           |    Limit=1   |    Limit=2   |   Limit=3    |
Brcode,Code|Balace|Overdue|Balace|Overdue|Balace|Overdue|


What I have tried:

pivot query might be use full but i don't know how to use it.
Posted
Updated 6-Apr-20 5:15am

Just use a JOIN to select rows with matching Brcode and Code values from the three SELECT operations: SQL Joins[^]

Something like:
SQL
SELECT a.Brcode, a.Code, a.Balance, a.Overdue, b.Balace, b.Overdue, c.Balace, c.Overdue
FROM (SELECT ...) a
JOIN (SELECT ...) b ON a.Brcode = b.Brcode AND a.Code = b.Code
JOIN (SELECT ...) C ON a.Brcode = c.Brcode AND a.Code = c.Code;
 
Share this answer
 
Comments
Maciej Los 6-Apr-20 11:46am    
+5!
You don't need to PIVOT, you need to JOIN[^] the tables; and I would also recommend that you use CTEs[^] 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)
SQL
; 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.
SQL
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.
SQL
; 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 aLEFT 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

SQL
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[^]
 
Share this answer
 
Comments
Maciej Los 6-Apr-20 11:45am    
+5!
Try this:
SQL
SELECT BrCode, Code, Balance1 = SUM(CASE WHEN [limit]=1 THEN BALANCE ELSE 0 END),
    Overdue1 = SUM(CASE WHEN [limit]=1 THEN Overdue ELSE 0 END),
    Balance2 = SUM(CASE WHEN [limit]=2 THEN BALANCE ELSE 0 END),
    Overdue2 = SUM(CASE WHEN [limit]=2 THEN BALANCE ELSE 0 END),
    Balance3 = SUM(CASE WHEN [limit]=3 THEN BALANCE ELSE 0 END),
    Overdue3 = SUM(CASE WHEN [limit]=3 THEN Overdue ELSE 0 END)
FROM
(
    SELECT BrCode, Code, SUM((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) Overdue, [limit]
    FROM LON20200331 
    WHERE AcSts<>9
    GROUP BY BrCode, Code, [limit]
    UNION ALL
    SELECT BrCode, Code, SUM((ClearBal+AccumuInt+clgamt)) AS BALANCE,sum(OverdueAmt) Overdue, [limit]
    FROM ADV20200331
    WHERE AcSts<>9
    GROUP BY BrCode, Code, [limit]
) T
GROUP BY BrCode, Code


or this:

SQL
SELECT BrCode, Code, pvt1.[1] AS Balance1, pvt2.[1] AS Overdue1,
    pvt1.[2] AS Balance2, pvt2.[2] AS Overdue2,
    pvt1.[3] AS Balance3, pvt2.[3] AS Overdue3 
FROM
(
    SELECT BrCode, Code, [1], [2], [3]
    FROM
    (
        SELECT BrCode, Code, [limit], SUM((ClearBal+AccumuInt+clgamt)) AS BALANCE
        FROM LON20200331  AS LON INNER JOIN ADV20200331 AS ADV ON LON.BrCode = ADV.BrCode AND LON.Code = ADV.Code
        WHERE AcSts<>9
        GROUP BY BrCode, Code, [limit]
    ) dt1
    PIVOT(SUM(BALANCE) FOR [limit] IN([1], [2], [3])) pt1
) pvt1 INNER JOIN 
(
    SELECT BrCode, Code, [1], [2], [3]
    FROM
    (
        SELECT BrCode, Code, [limit], SUM(OverdueAmt) AS Overdue
        FROM LON20200331  AS LON INNER JOIN ADV20200331 AS ADV ON LON.BrCode = ADV.BrCode AND LON.Code = ADV.Code
        WHERE AcSts<>9
        GROUP BY BrCode, Code, [limit]
    ) dt2
    PIVOT(SUM(Overdue) FOR [limit] IN([1], [2], [3])) pt2
) AS pvt2 ON pvt1.BarCode = pvt2.BarCode AND pvt1.Code = pvt2.Code
 
Share this answer
 
Comments
MadMyche 6-Apr-20 11:39am    
+5Taking it to the next step.... I ran out of time :)
Maciej Los 6-Apr-20 11:45am    
Thank you.
Hemil Gandhi 7-Apr-20 8:29am    
Thanks your query really worked.
Maciej Los 7-Apr-20 9:14am    
You're very welcome.
I'd suggest to accept all valuables answers (use green button).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900