How to use the simple Dynamic pivot in SQL Server (Dynamic Columns)

How to use the simple Dynamic pivot in SQL Server (Dynamic Columns)

Declare @SampleTable as table (Sno int,Names nvarchar(50),Subjects nvarchar(50),

Marks int,Location nvarchar(50));

insert into @SampleTable VALUES(1,'Lokesh','Maths',95,'Hyderabad');

insert into @SampleTable VALUES(2,'Lokesh','Science',25,'Hyderabad');

insert into @SampleTable VALUES(3,'Krishna','Maths',59,'Chennai');

insert into @SampleTable VALUES(4,'Krishna','Science',56,'Chennai');

insert into @SampleTable VALUES(5,'Google','Maths',25,'UnNone');

insert into @SampleTable VALUES(6,'Google','Science',80,'UnNone');

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @queryExecution AS NVARCHAR(MAX);

select @cols =SUBSTRING(( select distinct ',['+CONVERT(VARCHAR(50),convert(VARCHAR(19),Subjects,105))+']'

from @SampleTable for xml path('')),2,8000)

set @queryExecution='

Declare @SampleTable as table (Sno int,Names nvarchar(50),Subjects nvarchar(50),

Marks int,Location nvarchar(50));

insert into @SampleTable VALUES(1,''Lokesh'',''Maths'',95,''Hyderabad'');

insert into @SampleTable VALUES(2,''Lokesh'',''Science'',25,''Hyderabad'');

insert into @SampleTable VALUES(3,''Krishna'',''Maths'',59,''Chennai'');

insert into @SampleTable VALUES(4,''Krishna'',''Science'',56,''Chennai'');

insert into @SampleTable VALUES(5,''Google'',''Maths'',25,''UnNone'');

insert into @SampleTable VALUES(6,''Google'',''Science'',80,''UnNone'');

SELECT * FROM @SampleTable;

with CTE

as

(

      SELECT Names,Location,'+@cols+' from @SampleTable

      PIVOT

      (

            SUM(Marks) FOR Subjects IN ('+@cols+')  

      ) as pivottable

)

--This is PIVOT table result

SELECT Names,Location,SUM(Maths) Maths,SUM(Science)Science from CTE group by  Names,Location'

PRINT @queryExecution

execute(@queryExecution);