CODEWITHSUNDEEP
sqlsql-server
Data Guy
Data Guy

Reputation: 87

Pivot function in SQL and sum rows

I have this query: with cte as

(
Select distinct
Account,
Avg(TotalME_AUA)over (partition by sub.account) as avgAUA
from
(

SELECT Distinct  wf_pm_asset_db.account,
       Sum(wf_pm_asset_db.me_aua) OVER(PARTITION by wf_pm_asset_db.account, wf_pm_asset_db.yearmonth)  AS TotalME_AUA, wf_pm_asset_db.yearmonth
FROM   wf_pm_asset_db  

WHERE  (( ( wf_pm_asset_db.yearmonth ) > '201512' )) 
and wf_pm_asset_db.account in ('XXXXX0E','XXXXX0F','XXXXX0G', 'XXXXX0H')
GROUP  BY wf_pm_asset_db.account  , wf_pm_asset_db.yearmonth, me_aua
)  sub
group by Account ,totalME_AUA, sub.account,TotalME_AUA
)
Select distinct CASE
                 WHEN (LTRIM(RTRIM(ISM_HH_KEY)) <> '')--OR --(LTRIM(RTRIM(v.ISM_HH_KEY)) IS NOT NULL)
                    THEN LTRIM(RTRIM(ISM_HH_KEY))
                    ELSE LEFT(LTRIM(RTRIM(WF_PM_ACCT_det_DB.ACCOUNT)), 6)
                 END AS 'HH',
CTE.account,
SUM(AvgAUA) as 'Avg_AUA'
FROM CTE
left join wf_pm_acct_det_db
on CTE.account = wf_pm_acct_det_db.Account
Group By wf_pm_acct_det_db.ISM_HH_Key, LEFT(LTRIM(RTRIM(WF_PM_ACCT_det_DB.ACCOUNT)), 6), cte.account

and the results look like this:

HouseID Account number  Balance
HHID    Account 1   627.01
HHID    Account 2   829633.1317
HHID    Account 3   0
HHID    Account 4   -771703.1858

i want an output where i get this: 

HouseID Account 1   Account 2   Account 3   Account 4   Total
HHID    627.01      829633.1317     0      -771703.1858  58556.95583

i have tried pivoting with:

Select distinct CASE
                 WHEN (LTRIM(RTRIM(ISM_HH_KEY)) <> '')--OR --(LTRIM(RTRIM(v.ISM_HH_KEY)) IS NOT NULL)
                    THEN LTRIM(RTRIM(ISM_HH_KEY))
                    ELSE LEFT(LTRIM(RTRIM(WF_PM_ACCT_det_DB.ACCOUNT)), 6)
                 END AS 'HH',
account,
SUM(AvgAUA) as 'Avg_AUA'
From CTE
FROM CTE
left join wf_pm_acct_det_db
on CTE.account = wf_pm_acct_det_db.Account
Pivot
(
SUM(AvgAUA) 
for CTE.account in (account1, account2)
) as PT

however i get errors. can anyone advise on how to go about this. I use SQL server 2012

Upvotes: 2

Views: 286

Answers (2)

McNets
McNets

Reputation: 10807

Using your results table, can pivot using the next sentence:

declare @pv table (HouseID varchar(20), AccountNumber varchar(20),  Balance decimal(18,4));

insert into @pv values
('HHID','Account 1', 627.01),
('HHID','Account 2', 829633.1317),
('HHID','Account 3', 0),
('HHID','Account 4', -771703.1858);

select HouseID, Total, [Account 1], [Account 2], [Account 3], [Account 4]
from
(select HouseID, Total = SUM(Balance) over (partition by HouseID),
        AccountNumber, Balance from @pv) st
pivot
(
    SUM(Balance)
    FOR AccountNumber in ([Account 1], [Account 2], [Account 3], [Account 4])
) as PV;

+---------+------------+-----------+-------------+-----------+--------------+
| HouseID | Total      | Account 1 | Account 2   | Account 3 |   Account 4  |
+---------+------------+-----------+-------------+-----------+--------------+
| HHID    | 58556,9559 | 627,0100  | 829633,1317 | 0,0000    | -771703,1858 |
+---------+------------+-----------+-------------+-----------+--------------+

Check it: http://rextester.com/NURP25818

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Conceptually speaking, you just need to take your current query which gives you account information across rows, and pivot on the account number. Towards this end, we can define a second CTE and then pivot on that.

WITH cte2 AS (
    SELECT DISTINCT CASE WHEN (LTRIM(RTRIM(ISM_HH_KEY)) <> '')
                         THEN LTRIM(RTRIM(ISM_HH_KEY))
                         ELSE LEFT(LTRIM(RTRIM(WF_PM_ACCT_det_DB.ACCOUNT)), 6)
                    END AS 'HH',
           account,
           SUM(AvgAUA) AS 'Avg_AUA'
    FROM CTE
    LEFT JOIN wf_pm_acct_det_db
        ON CTE.account = wf_pm_acct_det_db.Account
    GROUP BY wf_pm_acct_det_db.ISM_HH_Key,
             LEFT(LTRIM(RTRIM(WF_PM_ACCT_det_DB.ACCOUNT)), 6),
             account
)

SELECT t.HH,
       MAX(CASE WHEN t.account = 'Account 1' THEN t.Avg_AUA END) AS [Account 1],
       MAX(CASE WHEN t.account = 'Account 2' THEN t.Avg_AUA END) AS [Account 2],
       MAX(CASE WHEN t.account = 'Account 3' THEN t.Avg_AUA END) AS [Account 3],
       MAX(CASE WHEN t.account = 'Account 4' THEN t.Avg_AUA END) AS [Account 4],
       (SELECT SUM(Avg_AUA) FROM cte2
        WHERE account IN ('Account 1', 'Account 2', 'Account 3', 'Account 4')) AS [Total]
FROM cte2 t
GROUP BY t.HH

Upvotes: 1

Related Questions