SQL Pivot not quite right

Question

Maybe I am just not awake enough...I know I have done something like this in the past and when I look at the other answers here I think I am doing the same thing but I am not getting the expected results.

I have this query:

SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    ,  SUM(P.[VAL]) as [Value]
FROM #caselist AS tPM
INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
--S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
        WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
        ELSE P.[CAT]
    END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
GROUP BY tPM.mast_rel, S.[RC_TRANS] 

which gives me these results:

I want to pivot them so I get a single Mast_Rel with three columns of the categories

select Mast_Rel,[1], [2], [3]
from
(

SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    ,  SUM(P.[VAL]) as [Value]
FROM #caselist AS tPM
INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
--S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
        WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
        ELSE P.[CAT]
    END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
GROUP BY tPM.mast_rel, S.[RC_TRANS] 
)
src
pivot
(
max(Category) for CategoryCount in ([1], [2], [3])
) piv

order by 1;

but instead of getting a single row, I am getting each one on its own row:

Additionally, I need to have a "total" for the Value column also on the pivot. So ultimately I would like a single record that shows:

Can anyone help me tweak my query to get the results I need? Thank you!

Edit: Here is a script that will create the data and the current results:

declare  @results table (Mast_Rel varchar(100), CategoryCount varchar(10), Category varchar(100) , [Value] varchar(100))

insert into @results (Mast_rel, CategoryCount, Category, [Value]) 
values

 ('1602030055590P2404','1','Money','80.00'),
('1602051033480P3481','1','Miscellaneous/other (None of the above)','1000.00'),
('1602051033480P3481','2','Personal accessories (incl serial jewelry)','5000.00'),
('1602051033480P3481','3','Radio, TV, and sound entertainment devices',''),
('1602070005106P2804','1','Miscellaneous/other (None of the above)',''),
('1602080020374P3352','1','Money','128.09'),
('1602080020374P3352','2','Radio, TV, and sound entertainment devices',''),
('1602132349110P5208','1','Money','160.00'),
('1602132349110P5208','2','Radio, TV, and sound entertainment devices',''),
('1602171004296P3848','1','Consumable Goods','21.73'),
('1602201425504P2876','1','Radio, TV, and sound entertainment devices',''),
('16022115223610P3282','1','Consumable Goods','60.00'),
('16022115223610P3282','2','Money','300.00'),
('16022115223610P3282','3','Narcotic Equipment/Paraphernalia','10.00'),
('1602250140284P2804','1','Money','165.00'),
('1602250140284P2804','2','Radio, TV, and sound entertainment devices',''),
('16022916203812P2702','1','Guns/Firearms',''),
('16022916203812P2702','2','Radio, TV, and sound entertainment devices','')


select Mast_Rel,[1], [2], [3]
from
(

SELECT 
     * from @results
)
src
pivot
(
max(Category) for CategoryCount in ([1], [2], [3])
) piv

order by 1;

Answer 1

You should be able to alter your original query to get the result you want. The problem lies with the GROUP BY and the SUM() in it. Since you are grouping by S.[RC_TRANS] for the SUM() you are returning multiple rows which is altering the final result of your PIVOT.

You could remove the GROUP BY in the inner subquery and use SUM() OVER() instead. Changing your original query to the following should get you the result you want:

select Mast_Rel,[1], [2], [3], [Value]
from
(
  SELECT 
      tPM.mast_rel as Mast_Rel
    , row_Number() over(Partition by tPM.Mast_rel Order by tPM.Mast_rel) as CategoryCount
    ,  S.[RC_TRANS] as [Category]
    -- change the following line
    ,  SUM(P.[VAL]) OVER(PARTITION BY tPM.Mast_rel)  as [Value]
  FROM #caselist AS tPM
  INNER JOIN [TIBURON].[PARSProperty] AS P ON tPM.[MAST_REL] = P.[MAST_REL]
  --S.RC_KEY equals combination of P.CAT-P.ART when P.CAT ='Y' otherwise just P.CAT = RC_KEY
  LEFT JOIN [TIBURON].[SSCTAB] AS S ON (CASE
          WHEN P.[CAT] = 'Y' THEN P.[CAT] + '-' + P.[ART]
          ELSE P.[CAT]
      END) = S.[RC_KEY] AND S.[RC_TYPE] = 'CP'
  WHERE P.[P_INVL] != 'EVD' and S.[RC_TRANS] is not null
)
src
pivot
(
  max(Category) for CategoryCount in ([1], [2], [3])
) piv
order by 1;

By changing from SUM(P.[VAL]) with a GROUP BY to SUM(P.[VAL]) OVER(PARTITION BY tPM.Mast_rel) you're getting the total sum for each tPM.Mast_rel which is what you're trying to return in the final result set. The SUM(P.[VAL]) OVER should calculate the same value for each row in the Mast_Rel which then will not create multiple rows in the final result set.

Answer 2

I would do conditional aggregation :

select mast_rel, 
       max(case when categorycount = 1 then category end), 
       max(case when categorycount = 2 then category end), 
       max(case when categorycount = 3 then category end),
       sum(value)
from @results r
group by mast_rel;