Is Unpivot (Not Pivot) functionality available in Linq to SQL? How?(在 Linq to SQL 中是否提供 Unpivot(非 Pivot)功能?如何?)
问题描述
我看过一些帖子,它们可以让您获取数据透视结果,但不能取消数据透视,需要知道是否有任何干净的方法可以实现?如果不是任何解决方法也可以吗?
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 ?
执行此操作以在 Management Studio 中查看 unpivot 结果
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
第一个 Select 语句的输出
The output for first Select statement
PaymentId EmployeeId RegularHours OvertimeHOurs
----------- ----------- ---------------------------------------
1 1 40 10
1 2 20 0
(2 row(s) affected)
第二个 Select 语句的输出 &这就是我要找的东西
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)
推荐答案
好的,我看不出有什么方法可以将它翻译成 SQL,下面是我想出的,但这都是执行的托管代码.
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.
或者...您可以简单地在 SQL 中创建一个视图.
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是
-- 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]
这篇关于在 Linq to SQL 中是否提供 Unpivot(非 Pivot)功能?如何?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:在 Linq to SQL 中是否提供 Unpivot(非 Pivot)功能?如何?
- 良好实践:如何重用 .csproj 和 .sln 文件来为 CI 创建 2022-01-01
- 带有服务/守护程序应用程序的 Microsoft Graph CSharp SDK 和 OneDrive for Business - 配额方面返回 null 2022-01-01
- 如何用自己压缩一个 IEnumerable 2022-01-01
- MoreLinq maxBy vs LINQ max + where 2022-01-01
- Web Api 中的 Swagger .netcore 3.1,使用 swagger UI 设置日期时间格式 2022-01-01
- WebMatrix WebSecurity PasswordSalt 2022-01-01
- 输入按键事件处理程序 2022-01-01
- C#MongoDB使用Builders查找派生对象 2022-09-04
- C# 中多线程网络服务器的模式 2022-01-01
- 在哪里可以找到使用中的C#/XML文档注释的好例子? 2022-01-01