Hello Community,
I'm not sure if I'm using the correct term when I say 'Transpose', but I would like to transpose/re-arrange a table.
For example, the current table, OptionsetMetadata looks like the following... this is just a snippet:
And I would like the table to be transposed to the 'doctored' table (again, not sure if that is the right word) to the following, see image for a snippet
The data for the tables are:
CREATE TABLE OptionsetMetadata (
EntityName varchar(50),
OptionSetName varchar(50),
Option varchar(50),
IsUserLocalizedLabel bit,
LocalizedLabelLanguageCode int,
LocalizedLabel varchar(100))
INSERT OptionsetMetadata VALUES
('activityparty','participationtypemask','1',CONVERT(bit, 'False'),1033,'Sender'),
('activityparty','participationtypemask','2',CONVERT(bit, 'False'),1033,'To Recipient'),
('activityparty','participationtypemask','3',CONVERT(bit, 'False'),1033,'CC Recipient'),
('activityparty','participationtypemask','4',CONVERT(bit, 'False'),1033,'BCC Recipient'),
('activityparty','participationtypemask','5',CONVERT(bit, 'False'),1033,'Required attendee'),
('activityparty','participationtypemask','6',CONVERT(bit, 'False'),1033,'Optional attendee'),
('activityparty','participationtypemask','7',CONVERT(bit, 'False'),1033,'Organizer'),
('activityparty','participationtypemask','8',CONVERT(bit, 'False'),1033,'Regarding'),
('activityparty','participationtypemask','9',CONVERT(bit, 'False'),1033,'Owner'),
('activityparty','participationtypemask','10',CONVERT(bit, 'False'),1033,'Resource'),
('activityparty','participationtypemask','11',CONVERT(bit, 'False'),1033,'Customer'),
('activityparty','instancetypecode','0',CONVERT(bit, 'False'),1033,'Not Recurring'),
('activityparty','instancetypecode','1',CONVERT(bit, 'False'),1033,'Recurring Master'),
('activityparty','instancetypecode','2',CONVERT(bit, 'False'),1033,'Recurring Instance'),
('activityparty','instancetypecode','3',CONVERT(bit, 'False'),1033,'Recurring Exception'),
('activityparty','instancetypecode','4',CONVERT(bit, 'False'),1033,'Recurring Future Exception'),
('activityparty','donotpostalmail','1',CONVERT(bit, 'False'),1033,'Do Not Allow'),
('activityparty','donotpostalmail','0',CONVERT(bit, 'False'),1033,'Allow'),
('activityparty','donotfax','1',CONVERT(bit, 'False'),1033,'Do Not Allow'),
('activityparty','donotfax','0',CONVERT(bit, 'False'),1033,'Allow'),
('activityparty','donotphone','1',CONVERT(bit, 'False'),1033,'Do Not Allow'),
('activityparty','donotphone','0',CONVERT(bit, 'False'),1033,'Allow'),
('activityparty','ispartydeleted','1',CONVERT(bit, 'False'),1033,'Yes'),
('activityparty','ispartydeleted','0',CONVERT(bit, 'False'),1033,'No'),
('activityparty','donotemail','1',CONVERT(bit, 'False'),1033,'Do Not Allow'),
('activityparty','donotemail','0',CONVERT(bit, 'False'),1033,'Allow'),
('account','ts_primarysecondaryfocus','717750000',CONVERT(bit, 'False'),1033,'Tier 1'),
('account','ts_primarysecondaryfocus','717750001',CONVERT(bit, 'False'),1033,'Tier 2'),
('account','ts_primarysecondaryfocus','717750002',CONVERT(bit, 'False'),1033,'TBC'),
('account','customertypecode','1',CONVERT(bit, 'False'),1033,'Competitor'),
('account','customertypecode','2',CONVERT(bit, 'False'),1033,'Consultant'),
('account','customertypecode','3',CONVERT(bit, 'False'),1033,'Customer'),
('account','customertypecode','4',CONVERT(bit, 'False'),1033,'Investor'),
('account','customertypecode','5',CONVERT(bit, 'False'),1033,'Partner'),
('account','customertypecode','6',CONVERT(bit, 'False'),1033,'Influencer'),
('account','customertypecode','7',CONVERT(bit, 'False'),1033,'Press'),
('account','customertypecode','8',CONVERT(bit, 'False'),1033,'Prospect'),
('account','customertypecode','9',CONVERT(bit, 'False'),1033,'Reseller'),
('account','customertypecode','10',CONVERT(bit, 'False'),1033,'Supplier'),
('account','customertypecode','11',CONVERT(bit, 'False'),1033,'Vendor'),
('account','customertypecode','12',CONVERT(bit, 'False'),1033,'Other'),
('account','address1_addresstypecode','1',CONVERT(bit, 'False'),1033,'Bill To'),
('account','address1_addresstypecode','2',CONVERT(bit, 'False'),1033,'Ship To'),
('account','address1_addresstypecode','3',CONVERT(bit, 'False'),1033,'Primary'),
('account','address1_addresstypecode','4',CONVERT(bit, 'False'),1033,'Other'),
('account','accountclassificationcode','1',CONVERT(bit, 'False'),1033,'Default Value'),
('account','ts_ukrow','717750000',CONVERT(bit, 'False'),1033,'UK'),
('account','ts_ukrow','717750001',CONVERT(bit, 'False'),1033,'RoW'),
('account','preferredappointmenttimecode','1',CONVERT(bit, 'False'),1033,'Morning'),
('account','preferredappointmenttimecode','2',CONVERT(bit, 'False'),1033,'Afternoon'),
('account','preferredappointmenttimecode','3',CONVERT(bit, 'False'),1033,'Evening'),
('account','address2_freighttermscode','1',CONVERT(bit, 'False'),1033,'Default Value'),
('account','ts_introducertype','717750000',CONVERT(bit, 'False'),1033,'Primary'),
('account','ts_introducertype','717750001',CONVERT(bit, 'False'),1033,'Boutique'),
('account','ts_introducertype','717750002',CONVERT(bit, 'False'),1033,'T2 Generalists'),
('account','accountcategorycode','1',CONVERT(bit, 'False'),1033,'Preferred Customer'),
('account','accountcategorycode','2',CONVERT(bit, 'False'),1033,'Standard'),
('account','paymenttermscode','1',CONVERT(bit, 'False'),1033,'Net 30'),
('account','paymenttermscode','2',CONVERT(bit, 'False'),1033,'2% 10, Net 30'),
('account','paymenttermscode','3',CONVERT(bit, 'False'),1033,'Net 45'),
('account','paymenttermscode','4',CONVERT(bit, 'False'),1033,'Net 60'),
('account','preferredappointmentdaycode','0',CONVERT(bit, 'False'),1033,'Sunday'),
('account','preferredappointmentdaycode','1',CONVERT(bit, 'False'),1033,'Monday'),
('account','preferredappointmentdaycode','2',CONVERT(bit, 'False'),1033,'Tuesday'),
('account','preferredappointmentdaycode','3',CONVERT(bit, 'False'),1033,'Wednesday'),
('account','preferredappointmentdaycode','4',CONVERT(bit, 'False'),1033,'Thursday'),
('account','preferredappointmentdaycode','5',CONVERT(bit, 'False'),1033,'Friday'),
('account','preferredappointmentdaycode','6',CONVERT(bit, 'False'),1033,'Saturday'),
('account','businesstypecode','1',CONVERT(bit, 'False'),1033,'Default Value'),
('account','industrycode','1',CONVERT(bit, 'False'),1033,'Accounting'),
('account','industrycode','2',CONVERT(bit, 'False'),1033,'Agriculture and Non-petrol Natural Resource Extraction'),
('account','industrycode','3',CONVERT(bit, 'False'),1033,'Broadcasting Printing and Publishing'),
('account','industrycode','4',CONVERT(bit, 'False'),1033,'Brokers'),
('account','industrycode','5',CONVERT(bit, 'False'),1033,'Building Supply Retail'),
('account','industrycode','6',CONVERT(bit, 'False'),1033,'Business Services'),
('account','industrycode','7',CONVERT(bit, 'False'),1033,'Consulting'),
('account','industrycode','8',CONVERT(bit, 'False'),1033,'Consumer Services'),
('account','industrycode','9',CONVERT(bit, 'False'),1033,'Design, Direction and Creative Management'),
('account','industrycode','10',CONVERT(bit, 'False'),1033,'Distributors, Dispatchers and Processors'),
('account','industrycode','11',CONVERT(bit, 'False'),1033,'Doctor''s Offices and Clinics'),
('account','industrycode','12',CONVERT(bit, 'False'),1033,'Durable Manufacturing'),
('account','industrycode','13',CONVERT(bit, 'False'),1033,'Eating and Drinking Places'),
('account','industrycode','14',CONVERT(bit, 'False'),1033,'Entertainment Retail'),
('account','industrycode','15',CONVERT(bit, 'False'),1033,'Equipment Rental and Leasing'),
('account','industrycode','16',CONVERT(bit, 'False'),1033,'Financial'),
('account','industrycode','17',CONVERT(bit, 'False'),1033,'Food and Tobacco Processing'),
('account','industrycode','18',CONVERT(bit, 'False'),1033,'Inbound Capital Intensive Processing'),
('account','industrycode','19',CONVERT(bit, 'False'),1033,'Inbound Repair and Services'),
('account','industrycode','20',CONVERT(bit, 'False'),1033,'Insurance'),
('account','industrycode','21',CONVERT(bit, 'False'),1033,'Legal Services'),
('account','industrycode','22',CONVERT(bit, 'False'),1033,'Non-Durable Merchandise Retail'),
('account','industrycode','23',CONVERT(bit, 'False'),1033,'Outbound Consumer Service'),
('account','industrycode','24',CONVERT(bit, 'False'),1033,'Petrochemical Extraction and Distribution'),
('account','industrycode','25',CONVERT(bit, 'False'),1033,'Service Retail'),
('account','industrycode','26',CONVERT(bit, 'False'),1033,'SIG Affiliations'),
('account','industrycode','27',CONVERT(bit, 'False'),1033,'Social Services'),
('account','industrycode','28',CONVERT(bit, 'False'),1033,'Special Outbound Trade Contractors'),
('account','industrycode','29',CONVERT(bit, 'False'),1033,'Specialty Realty'),
('account','industrycode','30',CONVERT(bit, 'False'),1033,'Transportation'),
('account','industrycode','31',CONVERT(bit, 'False'),1033,'Utility Creation and Distribution')
SELECT * FROM OptionsetMetadata
And for the new table, 'doctored'..
CREATE TABLE doctored (
participationtypemask varchar(50),
instancetypecode varchar(50),
donotpostalmail varchar(50),
donotfax varchar(50),
donotphone varchar(50),
ispartydeleted varchar(50),
donotemail varchar(50),
ts_primarysecondaryfocus varchar(50),
customertypecode varchar(50),
address1_addresstypecode varchar(50),
accountclassificationcode varchar(50),
ts_ukrow varchar(50),
preferredappointmenttimecode varchar(50),
address2_freighttermscode varchar(50),
ts_introducertype varchar(50),
accountcategorycode varchar(50),
paymenttermscode varchar(50),
preferredappointmentdaycode varchar(50),
businesstypecode varchar(50),
industrycode varchar(100))
INSERT doctored VALUES
('Sender','Not Recurring','Do Not Allow','Do Not Allow','Do Not Allow','Yes','Do Not Allow','Tier 1','Competitor','Bill To','Default Value','UK','Morning','Default Value','Primary','Preferred Customer','Net 30','Sunday','Default Value','Accounting'),
('To Recipient','Recurring Master','Allow','Allow','Allow','No','Allow','Tier 2','Consultant','Ship To',NULL,'RoW','Afternoon',NULL,'Boutique','Standard','2% 10, Net 30','Monday',NULL,'Agriculture and Non-petrol Natural Resource Extraction'),
('CC Recipient','Recurring Instance',NULL,NULL,NULL,NULL,NULL,'TBC','Customer','Primary',NULL,NULL,'Evening',NULL,'T2 Generalists',NULL,'Net 45','Tuesday',NULL,'Broadcasting Printing and Publishing'),
('BCC Recipient','Recurring Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Investor','Other',NULL,NULL,NULL,NULL,NULL,NULL,'Net 60','Wednesday',NULL,'Brokers'),
('Required attendee','Recurring Future Exception',NULL,NULL,NULL,NULL,NULL,NULL,'Partner',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Thursday',NULL,'Building Supply Retail'),
('Optional attendee',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Influencer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Friday',NULL,'Business Services'),
('Organizer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Press',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Saturday',NULL,'Consulting'),
('Regarding',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Prospect',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Consumer Services'),
('Owner',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Reseller',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Design, Direction and Creative Management'),
('Resource',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Supplier',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Distributors, Dispatchers and Processors'),
('Customer',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Vendor',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Doctor''s Offices and Clinics')
SELECT * FROM doctored
I hope this request is more straight forward to accomplish that my last request