How to pivot this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to pivot this query

Author

Topic

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-24 : 09:25:39

See the example here:

http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources