SQL Unpivot Multiple Columns Data

SQL Unpivot multiple columns Data

An easier way to unpivot the data would be to use a CROSS APPLY to unpivot the columns in pairs:

select vendorid, orders, orders1
from pvt1
cross apply
(
  select emp1, sa union all
  select emp2, sa1
) c (orders, orders1);

See SQL Fiddle with Demo. Or you can use CROSS APPLY with the VALUES clause if you don't want to use the UNION ALL:

select vendorid, orders, orders1
from pvt1
cross apply
(
  values 
    (emp1, sa),
    (emp2, sa1)
) c (orders, orders1);

See SQL Fiddle with Demo

SQL unpivot of multiple columns

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

Unpivot pairs of associated columns to rows

CROSS APPLY (VALUES is the easiest way to unpivot usually, especially with multiple columns

SELECT
  t.ArtNr,
  v.Amount,
  v.Price
FROM YourTable t
CROSS APPLY (VALUES
  (Amount1, Price1),
  (Amount2, Price2),
  (Amount3, Price3)
) v(Amount, Price)

Some more tricks you can do with CROSS APPLY

SQL UnPivot Multiple Columns

What stops you from using union of 25 selects?

select ID, 'Req 1' as Requirement, Req1 as RequirementStatus, Req1Comment as Comments from t
union all
select ID, 'Req 2' as Requirement, req2 as RequirementStatus, req2Comment as Comments from t
union all
...
select ID, 'Req 25' as Requirement, req25 as RequirementStatus, req25Comment as Comments from t

SQL query to unpivot and union for multiple column

I think this is basically apply:

select v.month_year, a.sub, a.c_f, a.type, a.F_G, a.layer, v.value
from a cross apply
     (values ('dec_2020', a.dec_2020),
             ('jan_2021', a.jan_2021),
             ('feb_2021', a.feb_2021)
     ) v(month_year, value);
         
          

Unpivot multiple columns in Snowflake

If all you need to solve is for the table specified in the question - you can do it manually with a set of UNION ALL:

select NAME
  , 'ACTING_RATING' as SKILL, ACTING_RATING as SKILL_RATING, ACTING_COMMENTS as SKILL_COMMENTS
from DATA
union all
select NAME
  , 'COMEDY_RATING', COMEDY_RATING, COMEDY_COMMENTS
from DATA
union all
select NAME
  , 'MUSICAL_PERFORMANCE_RATING', MUSICAL_PERFORMANCE_RATING, MUSICAL_PERFORMANCE_COMMENTS
from DATA

---

Related Topics