文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >枢轴是连接行并将行转换为列的正确方式吗？

问枢轴是连接行并将行转换为列的正确方式吗？
EN

Stack Overflow用户
提问于 2018-01-31 12:12:33
回答 3查看 190关注 0票数 1

在MS Server 2016中

代码语言：javascript
复制
select 
    mdl_user.firstname + ' ' + mdl_user.lastname as studentname
    , mdl_quiz.name as quizname
    , mdl_quiz_attempts.attempt as attemptnumber
    , mdl_quiz_attempts.timefinish as attemptdate
    , mdl_quiz_attempts.sumgrades as attemptgrade
from mdl_quiz_attempts 
join mdl_quiz on mdl_quiz.id = mdl_quiz_attempts.quiz
join mdl_user on mdl_user.id = mdl_quiz_attempts.userid
order by mdl_user.id, mdl_quiz.name, mdl_quiz_attempts.attempt;

给出以下输出：

学生姓名34 30安德鲁小额所得税评税表1 2017/03/30 11:24 27 2017/03/30 12:15 32 Deon Lenark基本评税1 2017/02/28 10:22 Deon Lenark所得税评税1 2017/03/30 11:05 35 Chris Du Bill基本面评估12017/02/28 10:25 34 Chris Du Bill所得税评估1 2017/03/30 08:06 34 Craig Eben基本面评估1 2017/02/28 12:43 35 Craig Eben所得税评估1 2017/03/30 11:36 28 Craig Eben所得税评估2 2017/03/30 12:05 28魅力灰色基本面评估1 2017/02/28 08:33 34魅力灰色所得税评估1 2017/03/30 12:33 35 Jaco Cloete所得税评估1 2017/03/30 09:04 32 Dirk Schaan基本面评估1 2017/02/28 08:04 34 Dirk Schaan所得税评估12017/03/30 09:03 11 Dirk Schaan所得税评估2 2017/03/30 11:37 Deon Grobler所得税评估1 2017/03/30 07:49 27 Deon Grobler所得税评估2 2017/03/31 12:45 31 Ester Shannah基础评估1 2017/02/28 :32 36 Ester Shannah所得税评估2017/03/30 11:32 30 Ester Shannah所得税评估2 2017/03/30 12:21 35 Eve Haywood基本情况评估1 2017/02/28 07:36 Eve Haywood所得税评估1 2017/03/30 07:28 32 Fred Bezzo所得税评估1 2017/03/30 08*22 30 Fred Bezzo所得税评估2 2017/03/31 11:54 31

每个学生的名字已经完成了两个测试中的一个或两个(测试名称)。一些学生两次尝试一次或两次测试(如“尝试数”栏所示)。

我希望将数据“枢轴”，以便将每个学生的所有结果显示在一行中，如下所示：

代码语言：javascript
复制
=================================================================
studentname     Fundamentals Assessment     Income Tax Assessment
=================================================================
Mariy Vickery                               2017/03/30 06:50 - 26
-----------------------------------------------------------------
Jan Le Roux     2017/02/28 08:15 - 36       2017/03/30 07:34 - 32
-----------------------------------------------------------------
Andrew Small    2017/02/28 10:34 - 30       2017/03/30 11:24 - 27
                2017/03/30 12:15 - 32
-----------------------------------------------------------------
Deon Lenark     2017/02/28 10:22 - 29       2017/03/30 11:05 - 35
-----------------------------------------------------------------
Chris Du Bill   2017/02/28 10:25 - 34       2017/03/30 08:06 - 34
-----------------------------------------------------------------
Craig Eben      2017/02/28 12:43 - 35       2017/03/30 11:36 - 28
                                            2017/03/30 12:05 - 58
-----------------------------------------------------------------
Charm Grey      2017/02/28 08:33 - 34       2017/03/30 12:33 - 35
-----------------------------------------------------------------
Jaco Cloete     2017/03/30 09:04 - 32
-----------------------------------------------------------------
Dirk Schaan     2017/02/28 08:04 - 34       2017/03/30 09:03 - 11
                                            2017/03/30 11:37 - 30
-----------------------------------------------------------------
Deon Grobler                                2017/03/30 07:49 - 27
                                            2017/03/31 12:45 - 31
-----------------------------------------------------------------
Ester Shannah   2017/02/28 10:32 - 22       2017/03/30 11:32 - 30
                2017/02/28 15:20 - 36       2017/03/30 12:21 - 35
-----------------------------------------------------------------
Eve Haywood     2017/02/28 07:36 - 33       2017/03/30 07:28 - 32
-----------------------------------------------------------------
Fred Bezhout                                2017/03/30 08:22 - 30
                                            2017/03/31 11:54 - 31
-----------------------------------------------------------------

但是透视()函数执行聚合，我不想这样做--我想包括每个学生名字的所有成绩/尝试。

下面的透视()不返回我想返回的内容--当多次尝试时，它只返回一个尝试数(因为最大(Quiz_grade)聚合)。它重复了每行学生的名字，这看起来很难看：

代码语言：javascript
复制
select 
    * 
from (
    select 
        mdl_user.firstname + ' ' + mdl_user.lastname as fullname,
        mdl_quiz.name as quiz_name,  
        mdl_quiz_grades.grade as quiz_grade
    from mdl_quiz
    join mdl_quiz_grades on mdl_quiz.id = mdl_quiz_grades.quiz
    join mdl_course on mdl_course.id = mdl_quiz.course
    join mdl_user on mdl_user.id = mdl_quiz_grades.userid
) source
pivot ( 
    max(quiz_grade)
    for quiz_name in (
        [Fundamentals Assessment],
        [Income Tax Assessment]
    )
) as PIVT
order by fullname

因此，它看起来像透视()，这里使用的不正确的函数？我如何“枢轴”的数据和连接尝试日期和尝试等级列，如我的示例显示的？

sql-server
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2018-01-31 13:15:57

您可以尝试这样的东西和xml路径：

代码语言：javascript
复制
DECLARE @myTable TABLE(studentname VARCHAR(20),
      quizname VARCHAR(50),
      attemptnumber INT,
      attemptdate DATETIME,
      attemptgrade INT);


INSERT INTO @myTable VALUES
    ('Mariy Vickery', 'Income Tax Assessment', 1, '2017/03/30 06:50', 26)
    , ('Jan Le Roux', 'Fundamentals Assessment', 1, '2017/02/28 08:15', 36) 
    , ('Jan Le Roux', 'Income Tax Assessment', 1, '2017/03/30 07:34', 32) 
    , ('Andrew Small', 'Fundamentals Assessment', 1, '2017/02/28 10:34', 30) 
    , ('Andrew Small', 'Income Tax Assessment', 1, '2017/03/30 11:24', 27)
    , ('Andrew Small', 'Income Tax Assessment', 2, '2017/03/30 12:15', 32);

SELECT * 
FROM 
(
    SELECT 
        studentname as fullname,
        quizname as quiz_name,  
        STUFF( (SELECT + '  ' + CAST(attemptdate AS VARCHAR(50)) + ' - ' 
                    + CAST(attemptgrade AS VARCHAR(3)) AS quiz_grade
                FROM @myTable si 
                WHERE si.studentname = so.studentname and si.quizname = so.quizname
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''
        ) AS quiz_grade
    FROM @myTable so 
) SOURCE
PIVOT 
( 
    MAX(quiz_grade)
    FOR quiz_name IN ([Fundamentals Assessment],[Income Tax Assessment])
) AS PIVT
ORDER BY fullname
票数 2
EN

Stack Overflow用户

发布于 2018-01-31 14:07:24

试试这个-

代码语言：javascript
复制
DECLARE @myTable TABLE(studentname VARCHAR(20),
      quizname VARCHAR(50),
      attemptnumber INT,
      attemptdate DATETIME,
      attemptgrade INT);
DECLARE @myTable1 TABLE(studentname VARCHAR(20),
      [Fundamentals Assessment] VARCHAR(MAX),
      [Income Tax Assessment] VARCHAR(MAX));

INSERT INTO @myTable VALUES
    ('Mariy Vickery', 'Income Tax Assessment', 1, '2017/03/30 06:50', 26)
    , ('Jan Le Roux', 'Fundamentals Assessment', 1, '2017/02/28 08:15', 36) 
    , ('Jan Le Roux', 'Income Tax Assessment', 1, '2017/03/30 07:34', 32) 
    , ('Andrew Small', 'Fundamentals Assessment', 1, '2017/02/28 10:34', 30) 
    , ('Andrew Small', 'Income Tax Assessment', 1, '2017/03/30 11:24', 27)
    , ('Andrew Small', 'Income Tax Assessment', 2, '2017/03/30 12:15', 32)
    , ('Deon Lenark', 'Fundamentals Assessment', 1, '2017/02/28 10:22', 35)
    , ('Deon Lenark', 'Income Tax Assessment', 1, '2017/03/30 11:05', 34);


INSERT INTO @myTable1
SELECT studentname, 
    MAX([Fundamentals Assessment]) [Fundamentals Assessment], 
    MAX([Income Tax Assessment]) [Income Tax Assessment]
FROM
(
    SELECT studentname, IIF([Fundamentals Assessment] IS NOT NULL, 
        CONCAT([Fundamentals Assessment],' - ' ,attemptgrade),NULL) 
            AS [Fundamentals Assessment],
        IIF([Income Tax Assessment] IS NOT NULL, 
        CONCAT([Income Tax Assessment],' - ' ,attemptgrade),NULL) 
            AS [Income Tax Assessment], attemptnumber
    FROM
    (
        SELECT studentname, 
         IIF(quizname = 'Fundamentals Assessment',attemptdate,NULL) 
                AS [Fundamentals Assessment]
            ,IIF(quizname = 'Income Tax Assessment',attemptdate,NULL) 
                AS [Income Tax Assessment]
            ,attemptgrade, attemptnumber
        FROM @myTable
    ) AS A
)B
GROUP BY studentname, attemptnumber
ORDER BY studentname, attemptnumber


SELECT DISTINCT studentname, E.[Fundamentals Assessment], D.[Income Tax Assessment] 
    FROM @myTable1 p1
   CROSS APPLY ( 
        SELECT [Income Tax Assessment] + '  ' 
        FROM @myTable1 p2
        WHERE p2.studentname = p1.studentname 
        ORDER BY [Income Tax Assessment] 
        FOR XML PATH('') 
    )  D ( [Income Tax Assessment] )
    CROSS APPLY ( 
        SELECT [Fundamentals Assessment] + '  '
        FROM @myTable1 p2
        WHERE p2.studentname = p1.studentname 
        ORDER BY [Fundamentals Assessment] 
        FOR XML PATH('') 
    )  E ( [Fundamentals Assessment] )
票数 1
EN

Stack Overflow用户

发布于 2018-01-31 14:18:42

如果您需要一个动态版本

示例

代码语言：javascript
复制
Declare @SQL varchar(max) = '
Select *
 From (
        Select Distinct
               studentname 
              ,Item = quizname
              ,value = replace( Stuff((Select concat(format(attemptdate,''|yyyy/MM/dd HH:mm - ''),attemptgrade) From YourTable Where studentname=A.studentname and quizname=A.quizname For XML Path ('''')),1,1,'''') ,''|'',char(13))
         From  YourTable A
      ) A
 Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(quizname) 
                                               From YourTable  
                                               For XML Path('')),1,1,'')  + ') ) p'
--Print @SQL
Exec(@SQL);

返回

代码语言：javascript
复制
studentname     Fundamentals Assessment Income Tax Assessment
Andrew Small    2017/02/28 10:34 - 30   2017/03/30 11:24 - 27
                                        2017/03/30 12:15 - 32
Charm Grey      2017/02/28 08:33 - 34   2017/03/30 12:33 - 35
Chris Du Bill   2017/02/28 10:25 - 34   2017/03/30 08:06 - 34
Craig Eben      2017/02/28 12:43 - 35   2017/03/30 11:36 - 28
                                        2017/03/30 12:05 - 28
Deon Grobler    NULL                    2017/03/30 07:49 - 27
                                        2017/03/31 12:45 - 31
Deon Lenark     2017/02/28 10:22 - 29   2017/03/30 11:05 - 35
Dirk Schaan     2017/02/28 08:04 - 34   2017/03/30 09:03 - 11
                                        2017/03/30 11:37 - 30
Ester Shannah   2017/02/28 10:32 - 36   2017/03/30 11:32 - 30
                                        2017/03/30 12:21 - 35
Eve Haywood     2017/02/28 07:36 - 33   2017/03/30 07:28 - 32
Fred Bezzo      NULL                    2017/03/30 08:22 - 30
                                        2017/03/31 11:54 - 31
Jaco Cloete     NULL                    2017/03/30 09:04 - 32
Jan Le Roux     2017/02/28 08:15 - 36   2017/03/30 07:34 - 32
Mariy Vickery   NULL                    2017/03/30 06:50 - 26
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/48541906

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档