Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?
.net, linq, linq-to-sql, pivot, unpivot
Solution
Ok, I cant see a way you can do it where it is translated into SQL, below is what I have come up with but this is all performed managed code.
Or... you can simply create a view in SQL.
var payments = Payments.Select (p => new {
OvertimeHOurs = new {
p.PaymentId,
p.EmployeeId,
Hours = p.OvertimeHOurs,
PayType = "OvertimeHOurs"
},
RegularHours = new {
p.PaymentId,
p.EmployeeId,
Hours = p.RegularHours,
PayType = "RegularHours"
}
}
);
var result = payments.Select(a => a.OvertimeHOurs).Union(payments.Select (p => p.RegularHours));
result.Dump(); // LINQPad Method
SQL Generated is
-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'OvertimeHOurs'
DECLARE @p1 NVarChar(1000) = 'RegularHours'
-- EndRegion
SELECT [t4].[PaymentId], [t4].[EmployeeId], [t4].[OvertimeHOurs] AS [Hours], [t4].[value] AS [PayType]
FROM (
SELECT [t1].[PaymentId], [t1].[EmployeeId], [t1].[OvertimeHOurs], [t1].[value]
FROM (
SELECT [t0].[PaymentId], [t0].[EmployeeId], [t0].[OvertimeHOurs], @p0 AS [value]
FROM [payment] AS [t0]
) AS [t1]
UNION
SELECT [t3].[PaymentId], [t3].[EmployeeId], [t3].[RegularHours], [t3].[value]
FROM (
SELECT [t2].[PaymentId], [t2].[EmployeeId], [t2].[RegularHours], @p1 AS [value]
FROM [payment] AS [t2]
) AS [t3]
) AS [t4]
Problem
I have seen posts which would fetch you pivot results but not unpivot, Need to know if there is any clean way to achieve ? If not the any workaround would do as well ? Execute this to see unpivot results in Management Studio ``` CREATE TABLE [dbo].[Payment]( [PaymentId] [int] NOT NULL, [EmployeeId] [int] NOT NULL, [RegularHours] [decimal](18, 0) NULL, [OvertimeHOurs] [decimal](18, 0) NULL ) ON [PRIMARY] go insert into payment values (1, 1, 40, 10) insert into payment values (1, 2, 20, 0) go select * from payment select * from payment unpivot ([hours] for [paytype] in ([RegularHours], [OvertimeHOurs]))a ``` The output for first Select statement ``` PaymentId EmployeeId RegularHours OvertimeHOurs ----------- ----------- --------------------------------------- 1 1 40 10 1 2 20 0 (2 row(s) affected) ``` The output for second Select statement & this is what i am looking for ``` PaymentId EmployeeId hours paytype ----------- ----------- ----------------------------------------------------- 1 1 40 RegularHours 1 1 10 OvertimeHOurs 1 2 20 RegularHours 1 2 0 OvertimeHOurs (4 row(s) affected) ```