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);
SQL Server unpivot two columns
I would use APPLY
:
select v.*
from tbl1 t cross apply
(values ('A1', t.A1, t.cA1),
('A2', t.A2, t.cA2),
('A3', t.A3, t.cA3)
) v(A, value1, value2);
CROSS APPLY
implements a lateral join. This is much more powerful than merely unpivoting data, although unpivoting data is one simple way to start learning about lateral joins.
Related Topics
How to Create Xml Schema from an Existing Database in SQL Server 2008
Select Average from MySQL Table with Limit
Using in Clause in a Native SQL Query
Retrieve Rank from SQLite Table
Create a Global Static Variable in SQL Server
How to Find Duplicate Entries and Delete the Oldest Ones in SQL
Copy Multiple Records with a Master-Details Relationship
Deferrable Check Constraint in Postgresql
Convert Rows to Columns Using 'Pivot' in Mssql When Columns Are String Data Type
Oracle Trigger Ora-04098: Trigger Is Invalid and Failed Re-Validation
A Reliable Way to Verify T-SQL Stored Procedures
How to Run SQL Server Stored Procedures in Parallel
Detect SQL Island Over Multiple Parameters and Conditions
MySQL Error 1248 (42000): Every Derived Table Must Have Its Own Alias
How to Connect to an External Database from a SQL Statement or a Stored Procedure