quote:

---

Originally posted by sanjeevka08

but one another requirement is by this query, it will work only when there are 3 entries in leave table for the particular leaveid

SELECT leaveid,employeeid,leavedescription,
[1] AS date1,
[2] AS date2,
[3] AS date3
FROM (SELECT f.leaveid,f.employeeid,f.leavedescription,s.leavedate,
ROW_NUMBER() OVER (PARTITION BY f.leaveid ORDER BY s.leavedate) AS Seq
FROM Leave f
INNER JOIN LeaveDate s
ON s.leaveid = f.leaveid
where f.LeaveID=4
)t
PIVOT (MAX(leavedate) FOR Seq IN ([1],[2],[3]))p


user will increase days of leave as per his requirement. that means number of entries in second table varies. i know its a tough thing. but i am asking is it possible to apply loops or something to to increse number of columns as per number of leaves..

Sanjeev Kumar

---

quote:

---

Originally posted by sanjeevka08

sorry.. i am working in a company and the link is blocked here

Sanjeev Kumar

---

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results

Consider this example 
01.select * from 
02.( 
03.    
select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e 
04.    
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)  
05.) as t  
06.pivot  
07.( 
08.    
Count(OrderDate) for pivot_col in ([1996],[1997]) 
09.) as p
which shows total orders of each employees for years 1996 and 1997 

What if we want to have this for all the years available in the table
You need to use dynamic sql


This procedure is used to generate Dynamic Pivot results

The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post which can be used in SQL Server 2000

01.create procedure dynamic_pivot 
02.( 
03.@select varchar(2000), 
04.@PivotCol varchar(100),  
05.@Summaries varchar(100) 
06.) as 
07.declare @pivot varchar(max), @sql varchar(max) 
08.select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,') 
09.  
 
10. 
 
11.create table #pivot_columns (pivot_column varchar(100)) 
12. 
 
13.Select @sql='select distinct pivot_col from ('+@select+') as t'
14. 
 
15.insert into #pivot_columns 
16.exec(@sql) 
17. 
 
18.select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns 
19. 
 
20.select @sql= 
21.' 
22.    
select * from  
23.    
( 
24.        
'+@select+' 
25.    
) as t  
26.    
pivot  
27.    
( 
28.        
'+@Summaries+' for pivot_col in ('+@pivot+') 
29.    
) as p 
30.' 
31. 
 
32.exec(@sql)
Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases) 

Usage : 

1.EXEC dynamic_pivot 
2.'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e 
3.INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ', 
4.'Year(OrderDate)', 
5.'Count(OrderDate)'