How to Transpose a Table with T-SQL

  • 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

    The table for the doctored is:

    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

    Is this something fairly straight forward to accomplish?

    Attachments:
    You must be logged in to view attached files.
  • This looks like conditional aggregation, but your expected results have 10 rows, but the item you appear to want to aggregate on (Option) has 35 different, values, so i feel we're missing something here.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • This is close to what you want

    with cte as (

    select OptionsetName,

    LocalizedLabel,

    row_number()over(partition by optionsetname order by cast([option] as int)) as rn

    from OptionsetMetadata

    )

    select

    max(case when OptionSetName = 'participationtypemask' then LocalizedLabel end) as participationtypemask,

    max(case when OptionSetName = 'instancetypecode' then LocalizedLabel end) as instancetypecode,

    max(case when OptionSetName = 'donotpostalmail' then LocalizedLabel end) as donotpostalmail,

    max(case when OptionSetName = 'donotfax' then LocalizedLabel end) as donotfax,

    max(case when OptionSetName = 'donotphone' then LocalizedLabel end) as donotphone,

    max(case when OptionSetName = 'ispartydeleted' then LocalizedLabel end) as ispartydeleted,

    max(case when OptionSetName = 'donotemail' then LocalizedLabel end) as donotemail,

    max(case when OptionSetName = 'ts_primarysecondaryfocus' then LocalizedLabel end) as ts_primarysecondaryfocus,

    max(case when OptionSetName = 'customertypecode' then LocalizedLabel end) as customertypecode,

    max(case when OptionSetName = 'address1_addresstypecode' then LocalizedLabel end) as address1_addresstypecode,

    max(case when OptionSetName = 'accountclassificationcode' then LocalizedLabel end) as accountclassificationcode,

    max(case when OptionSetName = 'ts_ukrow' then LocalizedLabel end) as ts_ukrow,

    max(case when OptionSetName = 'preferredappointmenttimecode' then LocalizedLabel end) as preferredappointmenttimecode,

    max(case when OptionSetName = 'address2_freighttermscode' then LocalizedLabel end) as address2_freighttermscode,

    max(case when OptionSetName = 'ts_introducertype' then LocalizedLabel end) as ts_introducertype,

    max(case when OptionSetName = 'accountcategorycode' then LocalizedLabel end) as accountcategorycode,

    max(case when OptionSetName = 'paymenttermscode' then LocalizedLabel end) as paymenttermscode,

    max(case when OptionSetName = 'preferredappointmentdaycode' then LocalizedLabel end) as preferredappointmentdaycode,

    max(case when OptionSetName = 'businesstypecode' then LocalizedLabel end) as businesstypecode,

    max(case when OptionSetName = 'industrycode' then LocalizedLabel end) as industrycode

    from cte

    group by rn

    order by rn;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thom,

    Thanks for responding.

    Sorry for the delayed, for someone reason I'm not getting email alerts

    Can you please explain what you mean by Option?

  • carlton 84646 wrote:

    Thom,

    Thanks for responding.

    Sorry for the delayed, for someone reason I'm not getting email alerts

    Can you please explain what you mean by Option?

    You have a column called Option (in your DDL this generates an error, as OPTION is a reserved keyword, and you don't delimit identify said name).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Hi Mark,

    You are a hero.

    You seemed to have nailed it straight away.

    I can't thank you enough

  • You're welcome! Thanks for the feedback.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Mark,

    I wonder if you could help me with my other question? 😉

  • Sure, there's plenty of good folks here willing to help.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply