LINQ to SQL中是否有Unpivot（不是Pivot）功能？如何实现？

Question

LINQ to SQL中是否有Unpivot（不是Pivot）功能？如何实现？

7

我看到过一些帖子，可以获得数据透视表的结果，但不能逆转，想知道是否有清洁的方法来实现？如果没有，那么任何解决方法也可以。

在管理工作室中执行此操作以查看逆转结果

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)

第二个选择语句的输出，这就是我要找的。

PaymentId   EmployeeId  hours                                   paytype
----------- ----------- ----------------------------------------------------- 
1           1           40                                      RegularHours
1           1           10                                      OvertimeHOurs
1           2           20                                      RegularHours
1           2           0                                       OvertimeHOurs

(4 row(s) affected)

- Pravin Pawar

展示透视结果的链接在这里。 - Pravin Pawar

该操作正在寻找如何进行“解旋”操作。 - TheRealTy

该注释仅用于展示如何在L2S中旋转结果，我有兴趣获得L2S中的_unpivot_结果。问题描述展示了如何在T-SQL中实现它。 - Pravin Pawar

哈哈，抱歉我甚至没有意识到那是你自己的评论。我以为有人完全误读了你的主题。 - TheRealTy

抱歉，我不明白需要输出什么。是否应该将每个员工的所有付款小时数相加？例如：{1 => {RegularHours => 60; OvertimeHours => 10}}？ - Michael Schnerring

抱歉，我应该反过来吗？每个员工的所有付款类型的工时都应该加总起来吗？ - Michael Schnerring

3个回答

2

使用500个支付类型，您需要使用反射来提取您想要取消枢轴的字段，这可能会很慢，但如果您不处理大量数据，则可以有效。因此，您将无法让取消枢轴操作转换为SQL，您需要在将枢轴数据传输后在LINQ to Objects中执行它。（理论上，您可以编写代码生成器来创建查询，但我不确定SQL翻译处理500列的能力如何，或者数据库引擎如何处理500元素联合。）

因此，这是答案的类型-使用ShallowCopy方法可避免为每种支付类型设置枢轴（平面）数据：

public class UnpivotData {
    public int PaymentId { get; set; }
    public int EmployeeId { get; set; }
    public string PayType { get; set; }
    public decimal Hours { get; set; }

    public UnpivotData ShallowCopy() => (UnpivotData)this.MemberwiseClone();
}

现在，将数据转换为非透视表的扩展方法很简单-提取透视数据，然后针对每种支付类型返回一个新对象。该方法需要一个字符串数组来命名透视数据和一个lambda谓词来选择用于支付类型的字段名称。"Original Answer"翻译成"最初的回答"。

public static class IEnumerableExt {
    public static IEnumerable<UnpivotData> Unpivot<T>(this IEnumerable<T> src, string[] pivotFieldNames, string unPivotName, string unPivotValue, Func<string, bool> unpivotFieldNameFn) {
        var srcPIs = typeof(T).GetProperties();
        var srcPivotPIs = srcPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var srcUnpivotPIs = srcPIs.Where(pi => unpivotFieldNameFn(pi.Name)).ToList();
        var ansPIs = typeof(UnpivotData).GetProperties();
        var ansPivotPIs = ansPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var srcAnsPivotPIs = srcPivotPIs.Zip(ansPivotPIs, (spi, api) => new { spi, api }).ToList();
        var unPivotNamePI = ansPIs.First(pi => pi.Name == unPivotName);
        var unPivotValuePI = ansPIs.First(pi => pi.Name == unPivotValue);

        foreach (var d in src) {
            var ansbase = new UnpivotData();
            foreach (var sapi in srcAnsPivotPIs)
                sapi.api.SetValue(ansbase, sapi.spi.GetValue(d));

            foreach (var spi in srcUnpivotPIs) {
                var ans = ansbase.ShallowCopy();
                unPivotNamePI.SetValue(ans, spi.Name);
                unPivotValuePI.SetValue(ans, spi.GetValue(d));

                yield return ans;
            }
        }
    }
}

现在你可以使用“Unpivot”方法来反转任意数量的支付类型：

var result = payments.AsEnumerable().Unpivot(new[] { "PaymentId", "EmployeeId" }, "PayType", "Hours", fn => fn.EndsWith("Hours"));

- NetMage

2

var result = new List<UnpivotedDbRecord>();
Payments.ForEach(r =>
                    {
                        result.Add(new UnpivotedDbRecord
                                        {
                                            EmployeeId = r.EmployeeId,
                                            PaymentId = r.PaymentId,
                                            PaymentType = "Regular",
                                            Hours = r.RegularHours
                                        });
                        result.Add(new UnpivotedDbRecord
                                        {
                                            EmployeeId = r.EmployeeId,
                                            PaymentId = r.PaymentId,
                                            PaymentType = "Overtime",
                                            Hours = r.OvertimeHours
                                        });
                    });

- Michael Schnerring

那么，接收到的记录会是什么样子呢？你问题中的表格布局还不够动态，因为你有一个常规和加班列。或者更好的说法是：你所说的“通用”具体指什么？ - Michael Schnerring

1

System.Data.Linq.Table<T> 不包含 ForEach() 方法，您需要先调用 ToList() 方法，但这会生成性能较差的 SQL 语句，例如：

SELECT [t0].[PaymentId], [t0].[EmployeeId], [t0].[RegularHours], [t0].[OvertimeHOurs]
FROM [payment] AS [t0]

- TheRealTy

谢谢这个。很好知道。 :) - Michael Schnerring

通俗地讲，通过使用泛型，我意味着支持多种（500种）支付方式。 - Pravin Pawar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- TheRealTy · Accepted Answer

好的，我无法想象出如何将其翻译成SQL语言，下面是我想出来的代码，但这些代码都是用托管代码实现的。

或者……你可以在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]