SQL Server-可能的数据透视解决方案? [英] SQL Server - Possible Pivot Solution?

问题描述

我有一个非常简单的问题，很难在网上找到它.也许我正在搜索不正确的关键字，所以我想停下来问你们，因为您的网站一直为我的学习带来福气.请参见以下情形:

I have a simple enough issue that has been surprisingly difficult to locate online. Perhaps I am searching on improper keywords so I wanted to stop in and ask you guys because your site has been a blessing with my studies. See below scenario:

从谜题中选择学生，将count(*)作为总计，(未知变量:book1，book2，book3，book4等).

Select student, count(*) as Total, (the unknown variable: book1, book2, book3, book4, ect...) from mystudies.

基本上，我想做的是列出所有与唯一学生ID相匹配的书籍ID.有人能指出我正确的方向，良好的阅读水平或其他任何方面，以便我朝正确的方向迈出一步吗?我假设这将通过左联接完成(不确定如何执行x1，x2，x3部分)，然后仅通过唯一的学生证号(不重复)将二者链接起来，但每个在线点都指向枢轴，但枢轴出现将所有行放入列而不是一列. SQL Server 2005是首选平台.

Essentially all I would like to do is list out all books for a unique student id that matches the Total count. Could someone point me in the right direction, a good read or anything, so I can get a step going in the correct direction? I am assuming it would be done via a left join (not sure how to do the x1, x2, x3 part) and then just link the two by the unique student id number (no duplicates) but everyone online points to pivot but pivot appears to put all the rows into columns instead of one single column. SQL server 2005 is the platform of choice.

谢谢！

对不起

以下查询生成我的唯一ID(学生)和表中所有重复条目的学生人数:

The following query produces my unique id (the student) and the student's count for all duplicate entries in the table:

select student, count(*) as Total 
from mystudies
group by student order by total desc

我不知道的部分是如何在表的唯一ID(boookid)上创建左联接

the part I don't know is how to create the left join on the table unique id (boookid)

select mystudies1.student, mystudies1.total, mystudies2.bookid 
from  (  select student, count(*) as Total 
         from mystudies
         group by student
      ) mystudies1
      left join 
      (  select student, bookid 
         from mystudies
      ) mystudies2 
         on mystudies1.student=mystudies2.student
order by mystudies1.total desc, mystudies1.student asc

很显然，上面的行将产生类似于以下内容的结果:

Obviously the above row will produce results similar to the following:

Student    Total  BookID
000001    3        100001
000001    3        100002
000001    3        100003
000002    2        200001
000002    2        200002
000003    1        300001

但是我真正想要的是与以下内容相似的东西:

But what I actually want is something similar to the following:

Student    Total  BookID
000001     3      100001, 100002, 100003
000002     2      200001, 200002
000003     1      300001

我认为必须在左联接中完成，这样它才不会改变对学生执行的实际计数.谢谢！

I assumed it had to be done in a left join so that it didn't alter the actual count being performed on the student. thanks!

推荐答案

在SQL Server中，使用FOR XML Path方法:

In SQL-Server use the FOR XML Path Method:

SELECT  Student,
        Total,
        STUFF(( SELECT  ', ' + BookID
                FROM    MyStudies books
                WHERE   Books.Student = MyStudies.Student
                FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Books
FROM    (   SELECT  Student, COUNT(*) AS Total
            FROM    myStudies
            GROUP BY Student
        ) MyStudies

我之前已经对XML PATH方法的工作方式进行了完整的解释这里

I have previously given a full explanation of how the XML PATH Method works here. With a further improvement to my answer pointed out here

SQL Server小提琴

在MySQL和SQLite中，您可以使用

In MySQL AND SQLite you can use the GROUP_CONCAT function:

SELECT  Student, 
        COUNT(*) AS Total, 
        GROUP_CONCAT(BookID) AS Books
FROM    myStudies
GROUP BY Student

MySQL小提琴

SQLite小提琴

在Postgresql中，您可以使用 ARRAY_AGG 函数:

In Postgresql you can use the ARRAY_AGG Function:

SELECT  Student, 
        COUNT(*) AS Total, 
        ARRAY_AGG(BookID) AS Books
FROM    myStudies
GROUP BY Student

PostgreSQL小提琴

在oracle中，您可以使用 LISTAGG 功能

In oracle you can use the LISTAGG Function

SELECT  Student, 
        COUNT(*) AS Total, 
        LISTAGG(BookID, ', ') WITHIN GROUP (ORDER BY BookID) AS Books
FROM    myStudies
GROUP BY Student

Oracle SQL小提琴

这篇关于SQL Server-可能的数据透视解决方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！