terryh
Starting Member
1 Post |
Posted - 2015-04-24 : 08:36:12
|
Hi All, I have the below query that i need to pivot but cannot figure out how to do it. Any advice would be appreciated.
select * from ( select filteredphonecall.new_callertypename as "callertype", count(filteredphonecall.ActivityId) as "activityid", sum(CAST(filteredphonecall.new_initialenquiryDBSOther as tinyint)) as "DBSOther", sum(CAST(filteredphonecall.new_initialenquiryDBSfiltering as tinyint)) as "DBSfiltering", sum(CAST(filteredphonecall.new_initialenquiryDBSeligibility as tinyint)) as "DBSeligibility", sum(CAST(filteredphonecall.new_initialenquiryDBSbasicdisclosure as tinyint)) as "DBSbasicdisclosure", sum(CAST(filteredphonecall.new_initialenquirydbsbarring as tinyint)) as "DBSBarring", sum(CAST(filteredphonecall.new_initialenquiryroa as tinyint)) as "ROA", sum(CAST(filteredphonecall.new_initialenquiryconfirmingrecordssar as tinyint)) as "ConfirmRecSAR", sum(CAST(filteredphonecall.new_initialenquiryprisonwelfare as tinyint)) as "PrisonWelfare", sum(CAST(filteredphonecall.new_initialenquirywhattodisclose as tinyint)) as "WhatToDisclose", sum(CAST(filteredphonecall.new_initialenquiryenforcedsar as tinyint)) as "EnforcedSAR", sum(CAST(filteredphonecall.new_initialenquiryaboutrasservices as tinyint)) as "AboutRAS", sum(CAST(filteredphonecall.new_initialenquirybasicchecks as tinyint)) as "BasicChecks", sum(CAST(filteredphonecall.new_initialenquiryseekingemployment as tinyint)) as "SeekingEmp", sum(CAST(filteredphonecall.new_initialenquiryvolunteeringatnacro as tinyint)) as "VolAtNacro", sum(CAST(filteredphonecall.new_initialenquiryeducationtrainingoptions as tinyint)) as "EduTrainOpts", sum(CAST(filteredphonecall.new_initialenquiryinsurance as tinyint)) as "Insurance", sum(CAST(filteredphonecall.new_initialenquiryhefeadmissions as tinyint)) as "HEFEAdmin", sum(CAST(filteredphonecall.new_initialenquirycriminaljusticeprocess as tinyint)) as "CrimJust", sum(CAST(filteredphonecall.new_initialenquiryhousing as tinyint)) as "Housing", sum(CAST(filteredphonecall.new_initialenquirytravellingemigratingabroad as tinyint)) as "TravMigAbroad", sum(CAST(filteredphonecall.new_initialenquiryfinancebenefits as tinyint)) as "FinanceBenefits", sum(CAST(filteredphonecall.new_initialenquiryimmigrationtouk as tinyint)) as "ImmigrationUK", sum(CAST(filteredphonecall.new_initialenquirysialicensing as tinyint)) as "SIALicensing", sum(CAST(filteredphonecall.new_initialenquiryhealthsupportservices as tinyint)) as "HealthSuppServ", sum(CAST(filteredphonecall.new_initialenquirypoliceintelligence as tinyint)) as "Police", sum(CAST(filteredphonecall.new_initialenquirysecurityclearancesc as tinyint)) as "SecClearSC", sum(CAST(filteredphonecall.new_initialenquiryother as tinyint)) as "Other" from filteredphonecall (nolock) where filteredphonecall.CreatedOn >= getdate()-30 and filteredphonecall.new_callertypename is not null group by filteredphonecall.new_callertypename
The output of this is currently as below but i want to pivot so the rows and columns are the other way round.
activityID DBSOther DBSFiltering Employer 34 12 10 service 123 44 56 practitoner 58 12 23
Thanks,
Terry |
|