question

natongm avatar image
natongm asked

PIVOT Function

The query below gives me duplicate rows of ESTIMATEID because of different NetName associated with that EstimateID, so my TotalCost ends up being added multiple times. I believe using the PIVOT function might do the trick. How do I go about it? SELECT Market_Name, Salesperson, Client, ESTIMATE_ID, C.CALL_LETTERS AS NetName,ZONE_LIST, dtaAgency.Name, ISNULL(sum(case bcastmonth when '01-2015' then spotcount end),0) as JanSpots, ISNULL(sum(case bcastmonth when '02-2015' then spotcount end),0) as FebSpots, ISNULL(sum(case bcastmonth when '03-2015' then spotcount end),0) as MarSpots, 5. ISNULL(sum(case bcastmonth when '04-2015' then spotcount end),0) as AprSpots, ISNULL(sum(case bcastmonth when '05-2015' then spotcount end),0) as MaySpots, ISNULL(sum(case bcastmonth when '06-2015' then spotcount end),0) as JunSpots, ISNULL(sum(case bcastmonth when '07-2015' then spotcount end),0) as JulSpots, ISNULL(sum(case bcastmonth when '08-2015' then spotcount end),0) as AugSpots, 10. ISNULL(sum(case bcastmonth when '09-2015' then spotcount end),0) as SepSpots, ISNULL(sum(case bcastmonth when '10-2015' then spotcount end),0) as OctSpots, ISNULL(sum(case bcastmonth when '11-2015' then spotcount end),0) as NovSpots, ISNULL(sum(case bcastmonth when '12-2015' then spotcount end),0) as DecSpots, TotalCost 15. from (select SalesPerson,MARKET_NAME, client, estimate_ID, 20. CALL_LETTERS, BCASTMONTH, SUM(SPOTS) as spotcount, sum(Revenue) as rev, TotalCost,ZONE_LIST 25. from (SELECT A.SALESPERSON, A.MARKET_NAME, A.NAME AS CLIENT,A.ESTIMATE_ID, SUM(SPOTCOUNT) AS SPOTS,A.CALL_LETTERS, A.RADIO_COMBO, A.BCASTMONTH, SUM(REV) AS REVENUE, 30. TotalCost, ZONE_LIST FROM (SELECT DTASALESPERSON.SalesPerson, 35. CLIENT.Name, SURVEY.ESTIMATE_ID, SUM(SPOT.NUMBER_OF_SPOTS) AS SPOTCOUNT, --SURVEY.NIELSEN_MKTNAME, STATION.CALL_LETTERS, 40. STation.RADIO_COMBO, MARKET.MARKET_NAME, dtaAgency.Name AS AgencyName, LEFT(CONVERT(varchar, NewFlightStart + WEEK_NUMBER * 7 - 1, 101), 2) + '-' + RIGHT(CONVERT(varchar, NewFlightStart + WEEK_NUMBER * 7 - 1, 101), 4) AS BCastMonth, SUM(COST * NUMBER_OF_SPOTS) AS REV, 45. SURVEY.TotalCost, Survey.ZONE_LIST FROM ((((((SURVEY SURVEY INNER JOIN ESTIMATE ESTIMATE 50. ON (SURVEY.ESTIMATE_ID = ESTIMATE.ESTIMATE_ID)) INNER JOIN STATION STATION ON (STATION.SURVEY_ID = SURVEY.SURVEY_ID)) INNER JOIN DAYPART DAYPART ON (DAYPART.STATION_ID = STATION.STATION_ID)) 55. INNER JOIN dbo.SPOT SPOT ON (SPOT.AVAIL_ID = DAYPART.AVAIL_ID)) INNER JOIN dbo.MARKET MARKET ON (SURVEY.MARKET_ID = MARKET.MARKET_ID)) INNER JOIN DEMO DEMO ON (ESTIMATE.PRIMARY_DEMO = DEMO.DEMO_ID) INNER JOIN dtaSalesPerson ON ESTIMATE.AE_ID = DTASALESPERSON.SalesID 60. INNER JOIN CLIENT ON ESTIMATE.CLIENT_ID = CLIENT.CustID) LEFT JOIN dtaAgency ON (dtaAgency.AgencyID = ESTIMATE.AGENCY_ID) WHERE (SPOT.NUMBER_OF_SPOTS <> 0) 65. and (NewFlightStart between '2014-12-29' and '2015-12-27' or NewFlightEnd between '2014-12-29' and '2015-12-27') group by DTASALESPERSON.SalesPerson, 70. CLIENT.Name, STATION.RADIO_COMBO, SURVEY.ESTIMATE_ID, STATION.CALL_LETTERS, MARKET.MARKET_NAME, 75. NewFlightStart, NewFlightEnd, SPOT.WEEK_NUMBER,Survey.TotalCost,Survey.ZONE_LIST,dtaAgency.Name)A where BCastMonth in ('01-2015','02-2015','03-2015','04-2015','05-2015', '06-2015', '07-2015','08-2015','09-2015','10-2015','11-2015','12-2015') 80. GROUP BY A.NAME,A.ESTIMATE_ID,MARKET_NAME,SalesPerson, A.CALL_LETTERS,A.BCASTMONTH, A.RADIO_COMBO,TotalCost,ZONE_LIST)B group by 85. client, estimate_ID, SalesPerson, MARKET_NAME, BCASTMONTH,CALL_LETTERS,TotalCost,B.ZONE_LIST)C group by client, ESTIMATE_ID,C.CALL_LETTERS, SalesPerson, MARKET_NAME,TotalCost,ZONE_LIST order by ESTIMATE_ID
pivot
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As posted this is a mess. The formatting is all over the place but it looks like something went haywire somewhere along the way when you were posting. The big problem is we don't know what you are trying to do here. Why do you think you need pivot? Pivot doesn't group rows, it is used to convert rows to columns. Maybe that is what you want but from just a query and no explanation there is no way to know.
1 Like 1 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.