Hi Stefanie – Try using UNPIVOT command in the last query. You can get few examples around UNPIVOT command if you Bing it. Let me know if you need help in writing it.
Thanks, JitenRK From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, September 13, 2013 12:53 AM To: [email protected] Subject: [mssms] SQL SUM Rows - Status of a Specific Advertisment I am trying to create a new report very similar to the Status of the Specific Advertisment Report. I have to provide management an excel report that adds the total of waiting, running, retrying and reboot pending rows into a row called inProgress. I would be great if I could create a SCCM Report that does this for me, that way I do not have to recreate the excel report after each deployment. I have figured out how to get the total for those fields ( last select statement) but I am having trouble trying to figure out how to add that entry as a row and not a column. Heres what I have so far. Any help would be greatly appreciated. declare @Total int declare @Accepted int declare @Inprogress int select @Total=count(*), @Accepted=sum(case LastState WHEN 0 THEN 0 ELSE 1 end), @InProgress=count(case WHEN LastState IN (7,8,10,12) THEN 0 end) --when 7 then 2 LastStateName Waiting --when 8 then 2 LastStateName Running --when 10 then 2 LastStateName Retrying --when 12 then 2 LastStateName Reboot Pending --end) from v_ClientAdvertisementStatus where AdvertisementID=' '--@AdvertID select LastAcceptanceStateName as C013, count(*) as C015, ROUND(100.0*count(*)/@Total,1) as C016, AdvertisementID from v_ClientAdvertisementStatus where AdvertisementID=' '--@AdvertID group by LastAcceptanceStateName, AdvertisementID select LastStateName as C017, count(*) as C015, ROUND(100.0*count(*)/@Accepted,1) as C016, AdvertisementID from v_ClientAdvertisementStatus where AdvertisementID=' '--@AdvertID and LastState!=0 group by LastStateName, AdvertisementID select LastStateName as C017, count(*) as C015, @InProgress as InProgress, ROUND(100.0*count(*)/@Accepted,1) as C016, AdvertisementID from v_ClientAdvertisementStatus where AdvertisementID=''--@AdvertID<mailto:AdvertisementID=''--@AdvertID> and LastState!=0 group by LastStateName, AdvertisementID
