By Bhuban


2014-09-21 06:08:12 8 Comments

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<[email protected])
begin
    declare @sid nvarchar(10)
    set @sid=(select SubjectID from #tempSubject where [email protected])
    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.

1 comments

@Pieter Geerkens 2014-09-21 21:45:54

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 Questions

Sponsored Content

2 Answered Questions

[SOLVED] Investigating errors from strange query

1 Answered Questions

[SOLVED] How to run update queries saved in a permanent table?

  • 2019-07-30 11:33:15
  • Byron Louise Hudkin Festin
  • 45 View
  • 0 Score
  • 1 Answer
  • Tags:   sql-server ssms

2 Answered Questions

[SOLVED] Dynamic Parameters In Pivot

1 Answered Questions

[SOLVED] SQL 2005 Unused procedures

1 Answered Questions

[SOLVED] QUERY - pivot multiple columns, variable number of rows

1 Answered Questions

[SOLVED] MySQL Pivot Table Dynamic Rows

  • 2015-05-19 07:39:45
  • user3142200
  • 2874 View
  • 1 Score
  • 1 Answer
  • Tags:   mysql pivot

1 Answered Questions

[SOLVED] Oracle GoldenGate add trandata errors

3 Answered Questions

[SOLVED] Inserting and updating and selecting at thousands of times per second

Sponsored Content