0

I have written a dynamic pivot query for a column 'Total' which is working fine but I want to create dynamic pivot query on multiple columns in SQL Server, but it is showing me error if I use multiple columns in aggregate function.

My query for single column is like this

Declare @S  nvarchar(1024)
set @S=''

Select @S = @S + a.[Column] +',' 
from
    (SELECT distinct   
        ISNULL(dbo.tbSubjects.SubCode, 'NoColumnName') as [Column] 
     FROM         
        dbo.tbAttendance 
     INNER JOIN
        dbo.tbAttendanceMaster ON dbo.tbAttendance.MasterAID = dbo.tbAttendanceMaster.AtdID 
     INNER JOIN
        dbo.tbStudent ON dbo.tbAttendance.StID = dbo.tbStudent.StudentID 
     INNER JOIN
        dbo.tbSubjects ON dbo.tbAttendanceMaster.SubID = dbo.tbSubjects.SubID 
     LEFT OUTER JOIN
        dbo.tbSemester ON dbo.tbSubjects.SemID = dbo.tbSemester.SemID) as a

set @S = LEFT(@S, LEN(@S) - 1)
print @S

set @S = 'select Name,'+@S+' from(SELECT  distinct tbStudent.Name,   dbo.tbSubjects.SubCode, dbo.fnTotalDays(dbo.tbAttendanceMaster.SubID, 
dbo.tbStudent.StudentID) AS Total 

FROM         dbo.tbAttendance INNER JOIN
                      dbo.tbAttendanceMaster ON dbo.tbAttendance.MasterAID = dbo.tbAttendanceMaster.AtdID INNER JOIN
                      dbo.tbStudent ON dbo.tbAttendance.StID = dbo.tbStudent.StudentID INNER JOIN
                      dbo.tbSubjects ON dbo.tbAttendanceMaster.SubID = dbo.tbSubjects.SubID LEFT OUTER JOIN
                      dbo.tbSemester ON dbo.tbSubjects.SemID = dbo.tbSemester.SemID ) sq
                      pivot(sum(Total)   for SubCode IN('+@S+') ) as pt

'
print @S

Execute sp_Executesql @S
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mubashir Gul
  • 5
  • 1
  • If you want to do this using `pivot` you'll need to do separate pivots and join them. – Laurence Sep 03 '14 at 16:56
  • could you provide any example – Mubashir Gul Sep 03 '14 at 16:58
  • `;with q1 as (pivot query 1), q2 as (pivot query 2) select key, q1.agg_col1, q1.agg_col2, q2.agg_col1, q2.agg_col2 from q1 inner join q2 on q1.key = q2.key` – Laurence Sep 03 '14 at 17:00
  • possible duplicate of [SQL SERVER PIVOT table with joins and dynamic columns](http://stackoverflow.com/questions/18317732/sql-server-pivot-table-with-joins-and-dynamic-columns) – M.Ali Sep 03 '14 at 17:01
  • 1
    Paging PIVOT expert @bluefeet, expertise with dynamic PIVOT is required ! :-) – marc_s Sep 03 '14 at 18:29

0 Answers0