gpt4 book ai didi

sql-server - 如何将列值转换为列?

转载 作者:行者123 更新时间:2023-12-02 10:26:42 24 4
gpt4 key购买 nike

我已经浏览了 Stackoverflow、在线图书和 Google 上的大量 SQL Pivot 示例,但我仍然不知道如何执行(我称之为)简单的 Pivot 操作。

Example 1

示例数据:

Name     Class       Score
======= ========== ======
Nick Chinese 80
Nick English 70
Nick Biology 85
Nick Maths 85
Kent Chinese 80
Kent Maths 90
Kent English 70
Kent Biology 85

所需输出 1 - 按类别透视,按名称聚合

Name     Chinese     English   Biology  Maths
======= ========== ======== ======= ======
Nick 80 70 85 85
Kent 80 70 85 90

注意:

In my head i imagine the syntax would be:

SELECT Score FROM Scores
GROUP BY Name
PIVOT BY Class

所需输出 2 - 按类别进行透视,总分

Name     Chinese     English   Biology  Maths
======= ========== ======== ======= ======
70 Nick
70 Kent
80 Nick
80 Kent
85 Nick Nick
85 Kent
90 Kent

注意:

In my head i imagine the syntax would be:

SELECT Name FROM Scores
GROUP BY Score
PIVOT BY Class

所需输出 3 - 按分数透视,按名称聚合

Name     70          80        85       90
======= ========== ======== ======= =====
Nick English Chinese Biology
Nick English Chinese Maths
Kent English Chinese Biology Maths

注意:

In my head i imagine the syntax would be:

SELECT Class FROM Scores
GROUP BY Name
PIVOT BY Score

所需输出 4 - 按分数进行透视,按类别聚合

Class    70          80        85       90
======= ========== ======== ======= =====
Chinese Nick
Chinese Kent
English Nick
English Kent
Biology Nick
Biology Kent
Maths Nick Kent

In my head i imagine the syntax would be:

SELECT Name FROM Scores
GROUP BY Class
PIVOT BY Score

所需输出 5 - 按名称透视,按类别聚合

Class    Nick  Kent
======= ==== ====
Chinese 80 80
English 70 70
Biology 85 85
Maths 85 90

In my head i imagine the syntax would be:

SELECT Score FROM Scores
GROUP BY Class
PIVOT BY Name

所需输出 6 - 按名称透视,按分数聚合

Score  Nick     Kent
===== ======= =======
70 English English
80 Chinese Chinese
85 Biology Biology
85 Maths Biology
90 Maths

In my head i imagine the syntax would be:

SELECT Class FROM Scores
GROUP BY Score
PIVOT BY Name

注意:我不想要一个可以执行所有这些数据透视的单个查询。我正在使用示例数据和示例数据透视表,因此请使用作为我可能想要执行的数据透视表的示例。

另一个示例集

另一个示例可能是解析用户登录域的日志:

LoginDate          Username  MachineName
================= ======== ===========
20120901 8:49:22 iboyd obsidian
20120901 9:10:19 nbach president
20120901 13:07:18 nback nichpc
20120902 8:58:38 iboyd obsidian
20120202 9:14:44 nbach president
20120902 18:34:43 iboyd harpax
20120903 8:57:13 iboyd obsidian
20120904 20:03:55 iboyd harpax

所需输出 7 - 按 LoginDate 的日期部分进行透视,按用户名聚合:

Username  20120901   20120902  20120903  20120914
======== ========= ======== ======== ========
iboyd obsidian obsidian obsidian harpax
iboyd obsidian harpax obsidian harpax
nbach president president
nback nichpc president

In my head i imagine the syntax would be:

SELECT MachineName FROM Logins
GROUP BY Username
PIVOT BY CONVERT(varchar(50), LoginDate, 112) --yyyymmdd format

Or perhaps:

SELECT MachineName FROM Logins
GROUP BY Username
PIVOT BY CAST(LoginDate AS DATE)

我似乎无法理解 PIVOT 语法;为了告诉 SQL Server 哪些列值应该成为列,以及聚合发生在哪些列值上。

每个人似乎都想对列进行硬编码,或者调用一些 XML 查询。我只是想做一个枢轴!

<小时/>

另请参阅

<小时/>

真正的问题TM

我今天要解决的真正问题是“业务”给我的屏幕截图模型:

enter image description here

如果 SQL Server 语法对我来说相当明显,那么这可能是一个相当明显的查询:

SELECT 
JobName, ShiftName,
Firstname+' '+Lastname+' - '+BankCode
FROM Transactions
GROUP BY JobName, ShiftName
PIVOT BY TransactionDate

最佳答案

将枢轴运算符视为替换您的组。以下是示例 #1 和 3 的示例:

SELECT name, [Chinese], [English], [Biology], [Maths]
FROM scores s
PIVOT (
SUM(score)
FOR Class IN ([Chinese], [English], [Biology], [Maths])
) p

SELECT name, [70], [80], [85], [90]
FROM scores s
PIVOT (
MAX(class)
FOR score IN ([70], [80], [85], [90])
) p

关于sql-server - 如何将列值转换为列?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12287240/

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