I have this query result, in which every particular has its column to display the balances.
Id AccountNumber Particular Inst1_Balance Inst2_Balance Inst3_Balance Inst4_Balance Inst5_Balance
1 99921 Inst5 0.00 0.00 0.00 0.00 232.50
1 99921 Inst3 0.00 0.00 170.00 0.00 0.00
3 86123 Inst3 0.00 0.00 843.00 0.00 0.00
4 76543 Inst2 0.00 123.00 0.00 0.00 0.00
5 12323 Inst4 0.00 0.00 0.00 1000.00 0.00
5 12323 Inst2 0.00 75.00 0.00 0.00 0.00
5 12323 Inst1 2765.00 0.00 0.00 0.00 0.00
7 23243 Inst5 0.00 0.00 0.00 0.00 865.00
8 43467 Inst2 0.00 435.00 0.00 0.00 0.00
9 67543 Inst3 0.00 0.00 1234.00 0.00 0.00
10 33245 Inst2 0.00 111.00 0.00 0.00 0.00
11 88881 Inst2 0.00 222.00 0.00 0.00 0.00
12 99931 Inst1 767.00 0.00 0.00 0.00 0.00
12 99931 Inst2 0.00 2345.00 0.00 0.00 0.00
From the above table I want to consolidate the balances of the particulars into a single column.
Id AccountNumber Particular Balance
1 99921 Inst5 232.50
1 99921 Inst3 170.00
3 86123 Inst3 843.00
4 76543 Inst2 123.00
5 12323 Inst4 1000.00
5 12323 Inst2 75.00
5 12323 Inst1 2765.00
7 23243 Inst5 865.00
8 43467 Inst2 435.00
9 67543 Inst3 1234.00
10 33245 Inst2 111.00
11 88881 Inst2 222.00
12 99931 Inst1 767.00
12 99931 Inst2 2345.00
;WITH tmptable AS (
SELECT INS.CustomerId AS 'Id',
INS.Particular AS 'Particular',
CONCAT(CON.AccountNumber) AS 'AcctNumber',
COALESCE(SUM(case when particular = 'Inst1' then [Bal] else 0 end),0) AS 'Inst1Bal',
COALESCE(SUM(case when particular = 'Inst2' then [Bal] else 0 end),0) AS 'Inst2Bal',
COALESCE(SUM(case when particular = 'Inst3' then [Bal] else 0 end),0) AS 'Inst3Bal',
COALESCE(SUM(case when particular = 'Inst4' then [Bal] else 0 end),0) AS 'Inst4Bal',
COALESCE(SUM(case when particular = 'Inst5' then [Bal] else 0 end),0) AS 'Inst5Bal'
FROM [Installment] INS
LEFT JOIN Customer CONS
ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId,
CON.AccountNumber
INS.Particular
)
SELECT Id,
AcctNumber,
Particular,
CAST(Inst1Bal AS numeric(18,2)) AS 'Inst1_Balance',
CAST(Inst2Bal AS numeric(18,2)) AS 'Inst2_Balance',
CAST(Inst3Bal AS numeric(18,2)) AS 'Inst3_Balance',
CAST(Inst4Bal AS numeric(18,2)) AS 'Inst4_Balance',
CAST(Inst5Bal AS numeric(18,2)) AS 'Inst5_Balance'
FROM tmptable
So far, I have this code which displays the data based on the first table.I need help in consolidating the 5 balances column into a single column as shown on the second table. Can it be done using JOIN?
You current query is a pivot query. Looks like what you want is a simple qroup by query.
SELECT INS.CustomerId AS Id,
CON.AccountNumber AS AcctNumber,
INS.Particular AS Particular,
SUM([Bal]) AS Balance
FROM [Installment] INS
LEFT JOIN Customer CON
ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId,
CON.AccountNumber,
INS.Particular;