Sql-server – Dynamic Number of Columns in Pivot Table

pivotsql serversql-server-2008sql-server-2008-r2

I have a table to store StudentID, SubjectID, ObtainedMarks.

Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate pivot table for fixed subjectID like

SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  ([1],[2],[3],[4])
)as finalResult

This works but problem is 1,2,3 is not static it should come from same table

So I did something like

declare @minvalue int, @maxvalue int
declare @myArray varchar(max)
set @MyArray=''

select RowID=IDENTITY(int, 1,1), SubjectID into #tempSubject from [Exam].[ObtainedMarkEntry]
select * from #tempSubject
set @minvalue = (select min(RowID) from #tempSubject)
set @maxvalue = (select max(RowID) from #tempSubject)
while (@minvalue<=@maxvalue)
begin
    declare @sid nvarchar(10)
    set @sid=(select SubjectID from #tempSubject where RowID=@minvalue)
    set @myArray= @myArray + '[' + @sid + '], ' 
    set @minvalue= @minvalue+1
end
set @myArray= SUBSTRING(@myArray,0,len(@myArray))
drop table #tempSubject

declare @q as nvarchar(max)
SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks 
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  (@myArray)
)as finalResult

Now I am getting error at in(@myArray) can't I assign variable in this section.

Best Answer

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus:

if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry;
create table #ObtainedMarkEntry(
    StudentID       int not null, 
    SubjectID       int not null, 
    ObtainedMarks       int not null
);
insert #ObtainedMarkEntry(StudentID, SubjectID, ObtainedMarks)
values
        (1,100,90) ,(1,101,70)
       ,(2,100,65) ,(2,300,55)
;

then this SQL:

declare @columnsSrc nvarchar(max) = N'' 
       ,@columnsDst nvarchar(max) = N'' 
       ,@sql        nvarchar(max)
       ,@KeyColumns nvarchar(max) = N'StudentID'
       ,@compatibility int = (
            select top 1 compatibility_level from sys.databases
            where name = db_name()
            order by Name
       );
declare @GroupBy nvarchar(max) = 
--case when @compatibility <= 90 
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' + @KeyColumns + ')'    end
--end
case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end

;

select  
    @columnsSrc += nchar(10) + N',' + quotename(SubjectID)
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename(SubjectID) + N',0)) as ' 
                                               + quotename(SubjectID)
  from (
      select SubjectID
      from #ObtainedMarkEntry
      group by SubjectID
  ) as x
  order by x.SubjectID
;

set @sql = N'
select ' + 
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
from (
  select' + nchar(10) + 
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      SubjectID, ObtainedMarks
   from #ObtainedMarkEntry
) as j
pivot (
  sum(ObtainedMarks) for SubjectID in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
;

--print @sql;
exec sp_executesql @sql;
go

yields as desired:

StudentID   100         101         300
----------- ----------- ----------- -----------
1           90          70          0
2           65          0           55
Related Question