.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Jean Paul
SP
satyapriyanayak
Ashutosh Jha
ASPEvil

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Dynamic Vs non-dynamic PIVOT queries

Posted By:nishithraj       Posted Date: November 27, 2009    Points: 25    Category: DataBase    URL: http://revolution-of-web.blogspot.com/  
 

Dynamic Vs non-dynamic PIVOT queries.

PIVOT is a new concept in SQLServer. They are used for cross-tab queries.

Many of us might have the doubt. What is the scope of dynamic PIVOT While the inbuilt feature PIVOT is there in the SQLserver.

Here is a detailed example for them.
Let us create a sample table first.


CREATE TABLE [dbo].[employee](
[emp_id] [char](9) NOT NULL,
[fname] [varchar](20) NOT NULL,
[minit] [char](1) NULL,
[lname] [varchar](30) NOT NULL,
[job_id] [smallint] NOT NULL,
[job_lvl] [tinyint] NULL,
[pub_id] [char](4) NOT NULL,
[hire_date] [datetime] NULL CONSTRAINT [DF_employee_hire_date] DEFAULT (getdate())
) ON [PRIMARY]


Insert some data to the table.


INSERT INTO [Test].[dbo].[employee]([emp_id],[fname],[minit],[lname],[job_id],[job_lvl],[pub_id],[hire_date]) VALUES (1,'Manmohan','k','kumar',1,1,1,getdate() )
INSERT INTO [Test].[dbo].[employee]([emp_id],[fname],[minit],[lname],[job_id],[job_lvl],[pub_id],[hire_date]) VALUES (2,'Manmohan','k','kumar',2,2,2,getdate() )
INSERT INTO [Test].[dbo].[employee]([emp_id],[fname],[minit],[lname],[job_id],[job_lvl],[pub_id],[hire_date]) VALUES (3,'Manmohan','k','kumar',3,3,3,getdate() )
INSERT INTO [Test].[dbo].[employee]([emp_id],[fname],[minit],[lname],[job_id],[job_lvl],[pub_id],[hire_date]) VALUES (4,'Manmohan','k','kumar',4,4,4,getdate() )


Use the pivot concept of Sqlserver


SELECT
[fname]
,[minit]
,[lname]
,[job_lvl]
,[pub_id]
,[hire_date]
FROM [Test].[dbo].[employee]
PIVOT
(
COUNT (emp_id)
FOR job_id IN
( [2],[3])
) AS pvt


Here you can select the columns except the pivoted columns
Also We need to pass the pivot values every time. If there are 1000 records this is not possible. Inorder to avoid this drawback we can go for dynamic pivoting as shown below.


Create procedure sp_RuntimePivoting
(
@prmSelectedvalue varchar(2000),
@prmPivotedColumn varchar(100),
@prmSummaries varchar(100)
) as
Declare @pivotvalue varchar(max)
Declare @sql varchar(max)

--replacing the select value to the pivotcolumn
select @prmSelectedvalue =replace(@prmSelectedvalue,'select ','select '+@prmPivotedColumn+' as pivot_col,')

--A Temporary table to store the pivot columns which are dynamically generated
create table #tmppivotcols (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@prmSelectedvalue+') as t'

insert into #tmppivotcols exec(@sql)

select @pivotvalue=coalesce(@pivotvalue+',','')+'['+pivot_column+']'from #tmppivotcols

select @sql=
'
select * from
(
'+@prmSelectedvalue+'
) as t
pivot
(
'+@prmSummaries+' for pivot_col in ('+@pivotvalue+')
) as p
'

exec(@sql)


EXEC sp_RuntimePivoting
'SELECT [emp_id]
,[fname]
,[minit]
,[lname]
,[job_id]
,[job_lvl]
,[pub_id]
,[hire_date]
FROM [Test].[dbo].[employee] ',
'emp_id',
'Max(job_id)'
.

But remember one thing, that is we can pass only non-repeated values to the dynamic pivot query.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend