gpt4 book ai didi

.net - Linq to SQL中是否提供Unpivot(非枢轴)功能?如何?

转载 作者:行者123 更新时间:2023-12-03 20:18:17 25 4
gpt4 key购买 nike

我看过一些文章,这些文章会为您带来关键的结果,但并非毫无疑问,需要知道是否有任何干净的方法可以实现?如果不是,任何解决方法也一样吗?

执行此操作以在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语句的输出
PaymentId   EmployeeId  RegularHours                            OvertimeHOurs
----------- ----------- ---------------------------------------
1 1 40 10
1 2 20 0

(2 row(s) affected)

第二个Select语句的输出,这是我正在寻找的
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的方法,以下是我想出的方法,但这都是执行的托管代码。

或者...您可以简单地在SQL中创建 View 。

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]

关于.net - Linq to SQL中是否提供Unpivot(非枢轴)功能?如何?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10188774/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com