15,877,145 members
See more: , +
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

## Solution 1

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;```

Maciej Los 6-Apr-20 11:46am
+5!

## Solution 2

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)
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)
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
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

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[^]

Maciej Los 6-Apr-20 11:45am
+5!

## Solution 3

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]
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```

+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)

Top Experts
Last 24hrsThis month
 Richard Deeming 260 Pete O'Hanlon 60 Thomas Schröter 40 OriginalGriff 40 Andre Oosthuizen 40
 Pete O'Hanlon 1,315 OriginalGriff 1,010 Graeme_Grant 635 Richard Deeming 613 Dave Kreskowiak 489

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