Develop pivot table query

1

I am trying to develop a pivot table. Please see the attached image for required output. I am unable to design the pivot query for the required solution.
This is my sql query ::
select
b.SchoolName,
c.KaryakramName,
a.NikashaAmount
from TBL_NIKASHA a
inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
WHERE
a.NikashaType = 1 and c.karyakramType = 1
order by a.SchoolId;

Here, NikashaType & karyakramType come from user.
I have to show the result of this query in pivot table. What I have done so far::
declare
@columns nvarchar(max) = '',
@sql nvarchar(max) = '';

select
	@columns += QUOTENAME(c.KaryakramName) + ','
from TBL_NIKASHA a
inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
WHERE 
a.NikashaType = 1 and c.karyakramType = 1

SET @columns = left(@columns, len(@columns)-1);

set @sql = '
	select * from 
(
		
) t
pivot
(
	a.NikashaAmount,
	for c.KaryakramName in ('+ @columns+')
) as pivot_table
';

I don't know how to proceed further. Please help.
query11

2 
SELECT S.SchoolName
	,MAX(CASE WHEN K.KaryakramName = 'sports materials' THEN N.NikashaAmount END) AS Sports_Materials
	,MAX(CASE WHEN K.KaryakramName = 'salary management' THEN N.NikashaAmount END) AS Salary_Management
FROM TBL_NIKASHA N
	INNER JOIN TBL_SCHOOL S
		ON S.SchoolId = N.SchoolId
	INNER JOIN TBL_KARYAKRAM K
		ON N.KaryakramId = K.KaryakramId
WHERE N.NikashaType = 1
	AND K.karyakramType = 1
GROUP BY S.SchoolName;
1 Like
3

Thank you for the reply. But karyakramName is dynamic. I can't use static names for this.

4

hi

please see what i did .. i tried Dynamic Pivot like you .. modify my sql for your tables

image

declare
    @columns nvarchar(max) = '',
    @sql     nvarchar(max) = '';

select
	@columns += QUOTENAME(a.KaryakramName) + ','
from 
   ( select distinct KaryakramName from Starting_Data ) a 

SET @columns = left(@columns, len(@columns)-1);

set @sql = 'select SchoolName,'+@columns +' from 
              ( select SchoolName, KaryaKramName , NikashaAmount from Starting_Data ) t
                pivot
              (SUM(NikashaAmount) FOR KaryaKramName in  ('+@columns+') ) p '

print @sql 


exec (@sql)



drop table Starting_Data 

create table Starting_Data (SchoolName varchar(200), KaryaKramName varchar(200) , NikashaAmount int  ) 

insert into Starting_Data Values 
('LA   ','sports materials',20000	  ),
('LA   ','salary management',1000000  ),
('GDPS ','sports materials',20000	  ),
('GDPS ','salary management',1000000  ),
('SVMPS','sports materials',15000	  ),
('SVMPS','salary management',1500000  )


select 'Starting Data' , * from Starting_Data
1 Like
5

KaryakramName field data is dynamic. I don't have prior information of all the KaryakramName, this is only sample data. Thank You!!!