Search code examples
sql-servert-sqlunpivot

SQL unpivot of multiple columns

I would like the following wide table to be unpivotted but only where a user has a true value against the field, along with the appropriate date. enter image description here Current State:

CUSTOMER_ID First_Party_Email Third_Party_Email First_Party_Email_Date Third_Party_Email_Date
40011111 1 1 2021-01-22 04:38:00.000 2021-01-17 06:38:00.000
50022222 NULL 1 NULL 2021-01-18 04:38:00.000
80066666 1 NULL 2021-01-24 05:38:00.000 NULL
_______________ _______________________ _______________________ _______________________________ _______________________________

Required State:

Customer_ID Type Value Date
40011111 First_Party_Email 1 22/01/2021 04:38
40011111 Third_Party_Email 1 17/01/2021 06:38
50022222 Third_Party_Email 1 18/01/2021 04:38
80066666 First_Party_Email 1 24/01/2021 05:38
_______________________________________________________________________

Associated query to create table and my attempt that doesn't work:

create table Permissions_Obtained
(Customer_ID bigint
,First_Party_Email  bit
,Third_Party_Email  bit
,First_Party_Email_Date datetime    
,Third_Party_Email_Date datetime
)

insert into Permissions_Obtained
(Customer_ID
,First_Party_Email
,Third_Party_Email
,First_Party_Email_Date
,Third_Party_Email_Date
)
VALUES
(40011111,  1,      1,      '2021-01-22 04:38', '2021-01-17 06:38'),
(50022222,  NULL,   1,      NULL,               '2021-01-18 04:38'),
(80066666,  1,      NULL,   '2021-01-24 05:38', null)

select * 
from Permissions_Obtained

select 
customer_id, Permission
from Permissions_Obtained
unpivot
(
  GivenPermission
  for Permission in (
First_Party_Email, Third_Party_Email
)
) unpiv1, 
unpivot
(
  GivenPermissionDate
  for PermissionDate in (
First_Party_Email_Date, Third_Party_Email_Date
)
) unpiv2
where GivenPermission = 1

--drop table Permissions_Obtained

Any help would be massively appreciated. TIA

Solution

  • You cannot have multiple unpivots at the same time. Instead you can use Cross Apply or Inner join or union, union all or kind of joins depending on your requirement. I have added a sample answer for this using join and unpivot.

       SELECT 
       unpvt.Customer_ID 
       , [Type]
       ,  ISNULL(po.First_Party_Email ,po.Third_Party_Email) AS [Value] 
       ,CASE WHEN unpvt.Type = 'First_Party_Email' THEN po.First_Party_Email_Date
             ELSE  po.Third_Party_Email_Date  
             END AS  [Date]

     FROM   
        (
         SELECT 
           Customer_ID, First_Party_Email , Third_Party_Email  
           FROM Permissions_Obtained 
         ) p  
       UNPIVOT  
          (  [Value] FOR [Type]     IN   
               (First_Party_Email , Third_Party_Email )  
          )AS unpvt
          INNER JOIN  Permissions_Obtained [po] 
             on [po].Customer_ID = unpvt.Customer_ID

    Result