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.