3

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.

  • 2
    There is an article by @AaronBertrand -- Script to create dynamic PIVOT queries in SQL Server. Basically you have to use STUFF – DenisT Sep 21 '14 at 7:24
  • I would probably do the pivoting in the application/reporting layer, where it is easier. – Daniel Hutmacher Sep 21 '14 at 8:56
  • @DanielHutmacher: For a large and/or frequent data volume, that might be entirely inappropriate due to the network load. There are often valid reasons to pivot on the server side. – Pieter Geerkens Sep 21 '14 at 21:12
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.