CODEWITHSUNDEEP
sql-servert-sqlunpivot
DarkWolve
DarkWolve

Reputation: 3

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

Upvotes: 0

Views: 6281

Answers (2)

Charlieface
Charlieface

Reputation: 71298

When un-pivoting multiple columns, CROSS APPLY (VALUES is often the easiest and most effective solution.

It creates a virtual table per-row of the previous table, and therefore un-pivots it into separate rows.

SELECT
    p.Customer_Id,
    v.[Type],
    v.Value,
    v.Date
FROM Permissions_Obtained p
CROSS APPLY (VALUES
    ('First_Party_Email', p.First_Party_Email, p.First_Party_Email_Date),
    ('Third_Party_Email', p.Third_Party_Email, p.Third_Party_Email_Date)
) v([Type], Value, Date)
where v.Value IS NOT NULL;

Upvotes: 1

Gudwlk
Gudwlk

Reputation: 1157

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

Upvotes: 2

Related Questions