主页 / dba / 问题 / 264207
Accepted
learning
Asked: 2020-04-03 16:49:34 +0800 CST

将行转换为列并对一些列进行分组

  • 772

我看到了很多关于如何将行转换为列的示例，并且我能够做到这一点。这是具有以下内容的表：

Userid     action      date            TRACKING         zip        Subject
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        O        1/2/2020           Amz           30105       $1 Sales journey
cbg10        C        1/2/2020           Amz           30105       $1 Sales journey
cbg10        P        1/2/2020           Amz           30105       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        O        1/2/2020           Amz           45685       $1 Sales journey
kel64        C        1/2/2020           Amz           45685       $1 Sales journey 
kel64        C        1/2/2020           Amz           45685       $1 Sales journey
kel64        p        1/2/2020           Amz           45685       $1 Sales journey
cbg10        O        4/18/2020          TG            30105       Summer arrives
cbg10        P        4/18/2020          TG            30105       Summer arrives
cbg10        C        4/18/2020          TG            30105       Summer arrives

这就是我希望表格的样子：正如您所看到的，有些打开的电子邮件打开了 2 次。而不是像第一个表中那样有 2 行，而是按主题添加人员采取的操作

Userid     date      O(Open)  C(Click)  P(Purchase)  TRACKING  zip     Subject
cbg10    1/2/2020     2         1        1            Amz     30105    $1 Sales journey
kel64    1/2/2020     3         2        1            Amz     45685    $1 Sales journey
cbg10    4/2/2020     1         1        1            Amz     30105    Summer arrives

This is the code that I have, but it is not giving me what I need
select UserId,

max(case when Aciton = 'O' then 1 else 0 end) as [O(Open)],
max(case when Aciton = 'P' then 1 else 0 end) as [P(Paid)],
max(case when Aciton = 'C' then 1 else 0 end) as [C(Cash)],
 [Date], 
 Tracking,
 Zip
 from myTable
 GROUP BY UserId, [Date], [Tracking], [Zip]
 ORDER BY UserId
sql-server pivot

1 个回答

  1. Best Answer

    值得庆幸的是SQL Server，它有一个名为 a 的内置函数PIVOT，它应该完全符合您的要求。SQLServerTutorial.Net上有一篇很棒的文章，其中有一些很好的例子来说明如何使用它。

    我认为这个查询应该给你你正在寻找的东西。我为我的测试建立了一个table variable调用。：@Table

    SELECT UserID,
[Date],
[O] AS 'O(Open)', --The value in the [Action] Column called out below, becomes the column name up here
[C] AS 'C(Click)', --The value in the [Action] Column called out below, becomes the column name up here
[P] AS 'P(Purchase)', --The value in the [Action] Column called out below, becomes the column name up here
Tracking,
Zip,
[Subject]
FROM
(
    SELECT UserID,
    [Action],
    [Date],
    Tracking,
    Zip,
    [Subject]
    FROM @Table
) T
PIVOT
(
    COUNT([Action])
    FOR [Action] IN([O], [C], [P]) --Call out the values in the Action Column
) AS Pivot_Table

    对我来说，它产生了如下表：

    UserID  Date        O(Open) C(Click)    P(Purchase) Tracking    Zip     Subject
cbg10   2020-01-02  2       1           1           Amz         30105   $1 Sales journey
cbg10   2020-04-18  1       1           1           TG          30105   Summer arrives
kel64   2020-01-02  3       2           1           Amz         45685   $1 Sales journey

    如果您需要，我可以将其分解为更多细节，因为您所要求的内容有几个细微差别，主要是按多列分组。

    希望这可以帮助！

    • 1

相关问题