Linq 交叉表和分组-解网

问：

我有一组数据，如下所示：

ItemID | ProcessID | Sequence
Item 1 | ProcessA  | 1 
Item 1 | ProcessB  | 2 
Item 3 | ProcessA  | 1 
Item 2 | ProcessA  | 1 
Item 1 | ProcessC  | 3
Item 3 | ProcessB  | 2

等

我想创建一个 linq 查询，该查询将数据格式化为如下所示：

ItemID | Sequence 1 | Sequence 2 | Sequence 3 | Sequence N 
Item 1 | ProcessA   | ProcessB   | ProcessC 
Item 2 | ProcessA   |  
Item 3 | ProcessA   | ProcessB

等

有没有一种简单的方法可以做到这一点？

C# asp.net LINQ 实体框架透视

public static IQueryable GetProcessesForAllItems()
{
    var ctx = new CS3Entities();

    var allprocesses = (from pi in ctx.ProcessesItems
                        orderby pi.SequenceNumber descending
                        select new
                                   {
                                       pi.ItemID,
                                       pi.ProcessID,
                                       pi.SequenceNumber
                                   });

    var query = allprocesses
        .GroupBy(c => c.ItemID)
        .Select(g => new
                         {
                             ItemId = g.Key,
                             Seq100 = g.FirstOrDefault(c => c.SequenceNumber == 100).ProcessID,
                             Seq200 = g.FirstOrDefault(c => c.SequenceNumber == 200).ProcessID,
                             Seq300 = g.FirstOrDefault(c => c.SequenceNumber == 300).ProcessID,
                             Seq400 = g.FirstOrDefault(c => c.SequenceNumber == 400).ProcessID,
                             Seq500 = g.FirstOrDefault(c => c.SequenceNumber == 500).ProcessID,
                             Seq600 = g.FirstOrDefault(c => c.SequenceNumber == 600).ProcessID,
                             Seq700 = g.FirstOrDefault(c => c.SequenceNumber == 700).ProcessID,
                             Seq800 = g.FirstOrDefault(c => c.SequenceNumber == 800).ProcessID,
                             Seq900 = g.FirstOrDefault(c => c.SequenceNumber == 900).ProcessID,
                             //etc
                         });
    return query;

}

1赞 Arion 2/22/2013 #2

我会使用纯sql方法来解决这个问题。所以这里有一个建议给你：

测试数据

CREATE TABLE #T
(
    ItemID VARCHAR(100),
    ProcessID VARCHAR(100), 
    Sequence INT
)
INSERT INTO #T
VALUES
    ('Item 1','ProcessA',1), 
    ('Item 1','ProcessB',2),
    ('Item 3','ProcessA',1),
    ('Item 2','ProcessA',1),
    ('Item 1','ProcessC',3),
    ('Item 3','ProcessB',2)

透视表的列

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
SELECT
    ROW_NUMBER() OVER(PARTITION BY Sequence ORDER BY Sequence) AS RowNbr,
    'Sequence '+CAST(t.Sequence AS VARCHAR(10)) AS Sequence
FROM
    #T AS t
)
SELECT @cols=STUFF
(
    (
        SELECT
            ',' +QUOTENAME([Sequence])
        FROM
            CTE
        WHERE CTE.RowNbr=1
        FOR XML PATH('')
    )
,1,1,'')

执行动态透视

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        t.ItemID,
        t.ProcessID,
        ''Sequence ''+CAST(t.Sequence AS VARCHAR(10)) AS Sequence
    FROM
        #T AS t
) AS SourceTable
PIVOT
(
    MAX(ProcessID)
    FOR Sequence IN ('+@cols+')
) AS p'

执行（@query）

删除临时表

DROP TABLE #T

上一个：在母版页的页脚中添加页面加载字符串 ASP.NET

下一个：SwiperJs - 如何显示 1 张半张幻灯片和 1 张完整幻灯片

Linq 交叉表和分组

Linq crosstab and grouping

评论