How to construct query to get a single row output in MS SQL SERVER 2012 -
i purely newbie on ms sql programming need on problem want subscription types other table making them column name if meet criteria. query working duplicate result set , that's not want these queries work. here's queries.
this sample output want.
divisionsname benchmark goalgetter tax credits aaa yes no yes bbb no yes yes code give me divisionsname benchmark goalgetter tax credits<br> aaa yes no no aaa no no yes bbb no yes no bbb no no yes the query is
select sectors.name sector_name, companies.name company_name, divisions.name primary_office, addresses.address1 address1, addresses.address2 address2, addresses.address3 address3, addresses.town town, addresses.postcode postcode, counties.name county, countries.name country, divtelephone.details telephone, membershiptypes.description membership_type, case contacts.titletype when 1 'none' when 2 'mr' when 3 'mrs' when 4 'miss' when 5 'ms' when 6 'master' when 7 'dr' when 8 'rev' when 9 'sir' when 10 'capt' when 11 'prof' else 'n/a' end salutation, contacts.firstname firstname, contacts.lastname lastname, contactemailaddress.details email_adress, case subscriptionhistories.subscriptiontype_id when 1 'yes' else 'no' end benchmark, case subscriptionhistories.subscriptiontype_id when 2 'yes' else 'no' end goalgetter, case subscriptionhistories.subscriptiontype_id when 3 'yes' else 'no' end taxcredits, case subscriptionhistories.subscriptiontype_id when 7 'yes' else 'no' end pmilicense, case subscriptionhistories.subscriptiontype_id when 8 'yes' else 'no' end '£2mfrompmievent', case subscriptionhistories.subscriptiontype_id when 9 'yes' else 'no' end pmiplus, case subscriptionhistories.subscriptiontype_id when 10 'yes' else 'no' end consultingtraining, case subscriptionhistories.subscriptiontype_id when 11 'yes' else 'no' end ep_outrightpurchaseofmodule1, case subscriptionhistories.subscriptiontype_id when 12 'yes' else 'no' end ep_ongoingsubscription, case subscriptionhistories.subscriptiontype_id when 13 'yes' else 'no' end useoflogo, case subscriptionhistories.subscriptiontype_id when 14 'yes' else 'no' end pmi, case subscriptionhistories.subscriptiontype_id when 15 'yes' else 'no' end businessbuilderforum, case subscriptionhistories.subscriptiontype_id when 16 'yes' else 'no' end businessconsulting, case subscriptionhistories.subscriptiontype_id when 17 'yes' else 'no' end '4psalesandmarketing', case subscriptionhistories.subscriptiontype_id when 18 'yes' else 'no' end peopleandleadership, case subscriptionhistories.subscriptiontype_id when 19 'yes' else 'no' end buildingabettertaxpractice, case subscriptionhistories.subscriptiontype_id when 20 'yes' else 'no' end sensationalservice, case subscriptionhistories.subscriptiontype_id when 21 'yes' else 'no' end systemisation, case subscriptionhistories.subscriptiontype_id when 22 'yes' else 'no' end taxconference2011, case subscriptionhistories.subscriptiontype_id when 23 'yes' else 'no' end paylesstax, case subscriptionhistories.subscriptiontype_id when 24 'yes' else 'no' end taxnda, case subscriptionhistories.subscriptiontype_id when 25 'yes' else 'no' end sbpurchasedpre2013, case subscriptionhistories.subscriptiontype_id when 26 'yes' else 'no' end sbstarterpack, case subscriptionhistories.subscriptiontype_id when 27 'yes' else 'no' end sbstandardbusinesscontent, case subscriptionhistories.subscriptiontype_id when 28 'yes' else 'no' end webhosting, case subscriptionhistories.subscriptiontype_id when 30 'yes' else 'no' end migration, case subscriptionhistories.subscriptiontype_id when 31 'yes' else 'no' end gsrb2bb2c, case subscriptionhistories.subscriptiontype_id when 32 'yes' else 'no' end migration, case subscriptionhistories.subscriptiontype_id when 33 'yes' else 'no' end eposoftwareupgrade, case subscriptionhistories.subscriptiontype_id when 34 'yes' else 'no' end epmodule2, case subscriptionhistories.subscriptiontype_id when 35 'yes' else 'no' end epmodule3, case subscriptionhistories.subscriptiontype_id when 36 'yes' else 'no' end epmodule4, case subscriptionhistories.subscriptiontype_id when 37 'yes' else 'no' end epmodule5, case subscriptionhistories.subscriptiontype_id when 38 'yes' else 'no' end epmodule6, case subscriptionhistories.subscriptiontype_id when 39 'yes' else 'no' end epmodule7, case subscriptionhistories.subscriptiontype_id when 40 'yes' else 'no' end epmodule8, case subscriptionhistories.subscriptiontype_id when 41 'yes' else 'no' end epmodule9, case subscriptionhistories.subscriptiontype_id when 42 'yes' else 'no' end epmodule10, case subscriptionhistories.subscriptiontype_id when 43 'yes' else 'no' end epmodule11, case subscriptionhistories.subscriptiontype_id when 44 'yes' else 'no' end epbonusmodule, case subscriptionhistories.subscriptiontype_id when 49 'yes' else 'no' end accountantschangingtheworld, case subscriptionhistories.subscriptiontype_id when 50 'yes' else 'no' end avnexcellence4star, case subscriptionhistories.subscriptiontype_id when 51 'yes' else 'no' end avnexcellence5star, case subscriptionhistories.subscriptiontype_id when 52 'yes' else 'no' end anvexcellence6star, case subscriptionhistories.subscriptiontype_id when 53 'yes' else 'no' end bwsfeeprotection, case subscriptionhistories.subscriptiontype_id when 54 'yes' else 'no' end coreemployeebenefits, case subscriptionhistories.subscriptiontype_id when 55 'yes' else 'no' end suspended contacts inner join divisions on divisions.id = contacts.division_id , divisiontype=1 , contacttype=2 , (contacts.isdeleted<>1 , divisions.isdeleted<>1) inner join companies on companies.id = divisions.company_id , (companies.isdeleted<>1) inner join sectors on sectors.id = companies.sector_id inner join addresses on addresses.id = divisions.company_id inner join counties on counties.id = addresses.county_id inner join countries on countries.id = counties.country_id left outer join subscriptionhistories on subscriptionhistories.entityid = divisions.id , (subscriptionhistories.startdate < getdate() , (subscriptionhistories.enddate null or subscriptionhistories.enddate > getdate())) inner join subscriptiontypes on subscriptiontypes.id = subscriptionhistories.subscriptiontype_id , subscriptiontypes.entitytype=2 left outer join communicationmethods divtelephone on divtelephone.entitytype = 2 , divtelephone.communicationmethodtype = 5 , divtelephone.entityid = divisions.id left outer join communicationmethods contactemailaddress on contactemailaddress.entitytype=3 , contactemailaddress.communicationmethodtype=1 , contactemailaddress.entityid = contacts.id inner join membershiphistories on membershiphistories.company_id = companies.id , (membershiphistories.startdate < getdate() , (membershiphistories.enddate null or membershiphistories.enddate > getdate())) inner join membershiptypes on membershiptypes.id = membershiphistories.membershiptype_id any appreciated. thank guys.
Comments
Post a Comment