SQL Pivot Dynamic Columns STUFF function empty cel

问题:

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

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

Edit: 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)

回答1:

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.

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)

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.

The code will be:

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;

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) |

回答2:

I have used this method to display a summary table from sales such as:

Product,Jan2013,Feb2013,.....

Using the FOR XML PATH('') method returns all the months, but they are not ordered. Can I sort them to follow the correct sequence? I attached my code below:

SET @cols = STUFF((SELECT distinct ',' + '[' + DATENAME(month,DateTime) + DATENAME(year,DateTime) +']' AS MonthYear 
                from DRGCase dc INNER JOIN Company c ON dc.HospitalId=c.CompanyId 
                WHERE dc.DischargeDate > @StartDate AND dc.DischargeDate <= DATEADD(DAY,1,@Enddate)
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');

SELECT @query='SELECT Hospital,' + @cols + 
    'FROM
    (
    SELECT c.CompanyName AS Hospital, DATENAME(month,DateTime) + DATENAME(year,DateTime) AS MonthYear,COUNT(*) AS Cases 

    FROM DRGCase dc INNER JOIN Company c ON dc.HospitalId=c.CompanyId 

    WHERE dc.DischargeDate > ''' + CONVERT(CHAR(10),@StartDate,126) + ''' AND dc.DischargeDate <= DATEADD(DAY,1,''' + CONVERT(CHAR(10),@EndDate,126) + ''')

    GROUP BY c.CompanyName, DATENAME(month,DateTime) + DATENAME(year,DateTime)
    ) ps PIVOT
    (
        sum(Cases)
        FOR MonthYear IN (' + @cols + ')
    )
    as pvt'
exec(@query)