SQL Pivot动态列STUFF函数空单元格 [英] SQL Pivot Dynamic Columns STUFF function empty cells

问题描述

使用STUFF函数执行SQL Pivot动态列，但没有得到我想要的结果

Doing SQL Pivot Dynamic Columns with STUFF function but not getting result I desire

这里是SQL Fiddle http://sqlfiddle.com/#!3/241c2/6/0

Here is SQL Fiddle http://sqlfiddle.com/#!3/241c2/6/0

我该怎么办才能摆脱所有空\空单元格?

What can I do to get rid of all the null\empty cells?

我希望显示的结果是这样的，其中每列显示每个学生分配给的课程列表，而没有一堆空白单元格

I would want the resulting display to be something like this where each column shows a list of classes each student is assigned to without a bunch of blank cells

Tom     Harry       Mary        Sue     Paul
Algebra     Algebra     Algebra     Algebra     Geometry
French      French      Spanish     Spanish     Art
Biology     Physics             Physics     Biology

已请求在此处显示代码:

request was made to show code here:

create table clsassin

(

    ClassID int,

    AssignID int,

    ClsNm varchar(10),

    StudntNm varchar(10),

)



insert into clsassin values (1, 1, 'Algebra', 'Tom')

insert into clsassin values (1, 2, 'Algebra', 'Harry')

insert into clsassin values (1, 3, 'Algebra', 'Mary')

insert into clsassin values (1, 4, 'Algebra', 'Sue')

insert into clsassin values (2, 5, 'Geometry', 'Paul')

insert into clsassin values (3, 6, 'French', 'Harry')

insert into clsassin values (3, 7, 'French', 'Tom')

insert into clsassin values (4, 8, 'Spanish', 'Mary')

insert into clsassin values (4, 9, 'Spanish', 'Sue')

insert into clsassin values (5, 10, 'Art', 'Paul')

insert into clsassin values (6, 11, 'Biology', 'Tom')

insert into clsassin values (6, 12, 'Biology', 'Paul')

insert into clsassin values (7, 13, 'Physics', 'Harry')

insert into clsassin values (7, 14, 'Physics', 'Sue')

insert into clsassin values (8, 15, 'History', 'Sue')

---

DECLARE @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX)



select @cols = STUFF((SELECT distinct ',' + QUOTENAME(StudntNm) 
                  FROM clsassin 

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)') 

,1,1,'')



set @query = 'SELECT ' + @cols + ' from 

    (

        select StudntNm, ClsNm
, ClassID                
        from clsassin

    ) x

    pivot 

    (

        min(ClsNm)

        for StudntNm in (' + @cols + ')

    ) p '



execute(@query)

推荐答案

原始查询的问题是，您将classid包括在PIVOT的select数据列表中.您有8个不同的classid值，然后将它们应用到数据透视表中时将按分组.

The problem with your original query is you are including the classid in the select list of data for the PIVOT. You have 8 different classid values, which will then be grouped by when applying the aggregate function in the pivot.

问题是，如果您排除classid并应用数据透视表，则每个学生将仅返回一个值-与min(ClsNm)

The problem is that if you exclude the classid and apply the pivot, you will return only one value per student - the one that matches the min(ClsNm)

由于要显示每个学生的每个班级，因此您应该考虑使用row_number()窗口函数而不是classid.如果应用row_number()并按studntNm划分数据，则将为每个学生的每个班级分配一个递增的数字，然后在汇总数据时将返回每一行.

Since you want to display every class for each student, then you should consider using the row_number() windowing function instead of classid. If you apply row_number() and partition the data by the studntNm, then you will assign an incremented number for each class per student, then when you aggregate the data you will return each row.

代码为:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(StudntNm) 
                  FROM clsassin 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
                select StudntNm, ClsNm,
                  row_number() over(partition by StudntNm
                                      order by ClsNm) rn
                from clsassin
            ) x
            pivot 
            (
                min(ClsNm)
                for StudntNm in (' + @cols + ')
            ) p '

execute sp_executesql @query;

请参见带演示的SQL提琴.这将为您提供结果:

See SQL Fiddle with Demo. This will give you the result:

|   HARRY |    MARY |     PAUL |     SUE |     TOM |
----------------------------------------------------
| Algebra | Algebra |      Art | Algebra | Algebra |
|  French | Spanish |  Biology | History | Biology |
| Physics |  (null) | Geometry | Physics |  French |
|  (null) |  (null) |   (null) | Spanish |  (null) |

这篇关于SQL Pivot动态列STUFF函数空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！