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);

Read More »

How to select the data from table with multiple values to single parameter using XQuery

How to select the data from table with multiple values to single parameter using XQuery 

By using this below query we can get the selected data from the table with comma separated text

From this input text we split the text using xml conversion and nodes accessing 

DECLARE @Tmep AS TABLE (Sno int IDENTITY,Employee varchar(50))

INSERT INTO @Tmep VALUES('Krishna')

INSERT INTO @Tmep VALUES('Ganga')

INSERT INTO @Tmep VALUES('Ramesh')

INSERT INTO @Tmep VALUES('Suresh')

SELECT * FROM @Tmep t

DECLARE @Employee AS nvarchar(100)='Krishna,Ramesh'

DECLARE @EmployeeXML AS XML

SET @EmployeeXML = cast(('<a>'+replace(@Employee,',' ,'</a><a>')+'</a>') AS XML)

SELECT * FROM @Tmep t

WHERE t.Employee IN

(

       SELECT

    A.value('.', 'varchar(max)')

    FROM @EmployeeXML.nodes('a') AS FN(A)

)

To execute and analysis the query copy the entire code and execute in the SQL server

Read More »