问题描述
我看过可以获取数据透视结果但不能获取数据透视结果的帖子,需要知道是否有任何干净的方法可以实现?如果不是,任何解决方法也可以吗?
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 中查看逆透视结果
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]