3
SELECT
 RegionManager,
 [Face-to-Face],
 [Phone],
 [Face-to-Face]+[Phone] as[Grand Total]
FROM
(select 
 ed.FRST_NM + ' ' + ed.LAST_NM AS RegionManager
 ,ACTVTY_TYP
 ,CALL_ID
 --,Month(CALL_DT) callmonth
FROM CODS_EDW.dbo.TERR_HIER th
LEFT JOIN CODS_EDW.dbo.[EMP_ROSTR] er ON er.[GEOG_ID] = th.GEOG_CD
LEFT JOIN CODS_EDW.dbo.EMP_DIM ed ON ed.EMPL_ID = er.EMPL_ID
LEFT JOIN CODS_EDW.dbo.[CALL] c
ON  c.TERR_CD = GEOG_CD + ' ' + GEOG_NM
WHERE th.SF_ID = 4
AND th.GEOG_LVL = 3
) source
PIVOT
(
    COUNT(CALL_ID)
    FOR ACTVTY_TYP
    IN ([Face-to-Face],[Phone] )
) AS pvtMonth
order by RegionManager

Using pivot i got result like this:

RegionManager   Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87

I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)

Row Labels  Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87
Grand Total     301 237 538
3

you have to put your query in CTE for it you have to use ROLLUP

;WITH CTE AS
(
-- Your Query
)

then

SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM CTE
GROUP BY ROLLUP(RowLabels);

SAMPLE DEMO

declare @temp table (RowLabels nvarchar(max), FacetoFace int, Phone int, GrandTotal int)

insert into @temp values ('Craig Irwin  ',73,6  ,79)
insert into @temp values ('Holly Murphy ',27,124,151)
insert into @temp values ('Julie Brown  ',37,28 ,65)
insert into @temp values ('Kevin Borth  ',52,35 ,87)
insert into @temp values ('Linda Krueger',46,23 ,69)
insert into @temp values ('Robert Bell  ',66,21 ,87)


SELECT
  RowLabels = ISNULL(RowLabels, 'Grand Total'),
  FacetoFace = SUM(FacetoFace),
  Phone = SUM(Phone),
  GrandTotal = SUM(GrandTotal)
FROM @temp
GROUP BY ROLLUP(RowLabels);
|improve this answer|||||
  • @Ram glad to help you :) – wiretext Sep 23 '15 at 11:52
  • Hi tinka ur query is working fine but i need out put like this JAN FEB MAR RegionManager FacetoFace Phone GrandTotal FacetoFace Phone GrandTotal FacetoFace Phone GrandTotal Craig Irwin 73 6 6 10 6 16 73 6 6 Holly Murphy 27 124 124 15 10 25 27 124 124 Julie Brown 37 28 28 20 28 48 37 28 28 Kevin Borth 52 35 35 50 35 85 52 35 35 Linda Krueger 46 23 23 46 23 69 46 23 23 Robert Bell 66 21 21 66 1 67 66 21 21 Grand Total 301 237 237 207 103 310 301 237 237 – Ram Sep 23 '15 at 12:08
  • 1
    @Ram i didn't get you could you please post a new Q then which will more clear with input data and expected output – wiretext Sep 23 '15 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.