Help with Transpose Multiple Columns

Carlton Patterson 741 Reputation points
2022-06-28T09:43:18.587+00:00

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:

215741-currenttable.png

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

215640-doctored.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 99,071 Reputation points MVP
    2022-06-28T22:01:02.65+00:00

    Here is a query. It only handles three of the columns, because I was lazy, but you should be table to add the rest.

    I'm a little bit in doubt whether the result actually makes sense. The data on row should represent an object with these specific properties. But that is up to you.

       ; WITH numbering AS (  
          SELECT *, row_number() OVER(PARTITION BY EntityName, OptionSetName ORDER BY "Option") AS rowno  
          FROM   OptionsetMetadata  
       )  
       SELECT MIN(CASE WHEN EntityName = 'activityparty' AND OptionSetName ='participationtypemask' THEN LocalizedLabel END) AS participationtypemask,  
              MIN(CASE WHEN EntityName = 'account' AND OptionSetName = 'ts_primarysecondaryfocus' THEN LocalizedLabel END) AS ts_primarysecondaryfocus,  
              MIN(CASE WHEN EntityName = 'account' AND OptionSetName = 'industrycode' THEN LocalizedLabel END) AS industrycode  
       FROM   numbering  
       GROUP BY rowno  
    
    0 comments No comments

  2. LiHong-MSFT 10,036 Reputation points
    2022-06-29T02:31:49.037+00:00

    Hi @Carlton Patterson
    Try this Dynamic Pivot query:

    DECLARE @sql_str VARCHAR(MAX)  
    DECLARE @spread_elements VARCHAR(MAX)  
      
    SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(OptionSetName)   
    FROM OptionsetMetadata GROUP BY OptionSetName  
    --PRINT @spread_elements  
      
    SET @sql_str =   
    '  
    ;WITH CTE AS  
    (  
     SELECT OptionSetName,LocalizedLabel  
           ,ROW_NUMBER()OVER(PARTITION BY EntityName,OptionSetName ORDER BY [Option]) AS RowNum   
     FROM OptionsetMetadata  
    )  
    SELECT RowNum,'+ @spread_elements +' FROM CTE  
    PIVOT (MAX(LocalizedLabel) FOR OptionSetName IN ( '+ @spread_elements +') ) AS P   
    '  
    --PRINT (@sql_str)  
    EXEC (@sql_str)  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments