SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 pivot rows to column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjeevka08
Starting Member

India
18 Posts

Posted - 10/17/2012 :  08:54:07  Show Profile  Reply with Quote
i have 2 tables
First table

leaveid employeeid leavedesc leavetype
----------------------------------------
1 3 notwell casual
2 3 tour casual

Second table

leaveid leavedate
------------------------
1 10/30/2012
1 10/31/2012
1 11/1/2012

i want to get data in an entire row corresponds to particular leaveid

leaveid employeeid leavedesc leavetype date1 date2 date3
----------------------------------------
1 3 notwell casual 10/30/2012 10/31/2012 11/1/2012

please provide me an suggestion

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/17/2012 :  09:33:37  Show Profile  Reply with Quote
Hi sanjeevka08

i would suggest posting your query so we can have a look

i am in the process of building a pivot table , and this is my queyr so far

Hope this helps


Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)



SELECT DISTINCT hst_merchnum as Fdmsaccountno,
[Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom],
ISNULL([Standard],0)
+ISNULL([CP],0)
+ISNULL([Contactless],0)
+ISNULL([Secure eCom],0)
+ISNULL([TBA],0)
+ISNULL([CHIP],0)
+ISNULL([MOTO],0)
+ISNULL([Non Secure eCom],0) as 'Grand Total'

FROM
(select Fact_Financial_History.hst_merchnum,
SUM(Fact_Financial_History.hst_sales_amt) AS [sales]
FROM

Dim_Outlet INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History.Plan_Key = Dim_Interchange_Tier_2.Plan_Code




Where Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and Dim_Interchange_Tier_2.Scheme =('mastercard')
and (hst_date_processed >= @date)
group by
Fact_Financial_History.hst_merchnum,
Dim_Interchange_Tier_2.Qualification_2

)as p
pivot
( MAX([Sales]) FOR Dim_Interchange_Tier_2.Qualification_2 in
([Standard],[CP],[Contactless],[Secure eCom],[TBA],[CHIP],[MOTO],[Non Secure eCom])) as pvt
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/17/2012 :  22:56:17  Show Profile  Reply with Quote

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



if you've to make it dynamic based on number of date values you can use below

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sanjeevka08
Starting Member

India
18 Posts

Posted - 10/18/2012 :  00:05:56  Show Profile  Reply with Quote
Thanks a lot. That works fine

Sanjeev Kumar
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2012 :  00:14:44  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sanjeevka08
Starting Member

India
18 Posts

Posted - 10/18/2012 :  00:17:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2012 :  00:44:05  Show Profile  Reply with Quote
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


I think you didnt read my full post
see the link i posted
it does exactly what you're asking for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sanjeevka08
Starting Member

India
18 Posts

Posted - 10/18/2012 :  00:46:40  Show Profile  Reply with Quote
sorry.. i am working in a company and the link is blocked here

Sanjeev Kumar
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2012 :  00:54:23  Show Profile  Reply with Quote
quote:
Originally posted by sanjeevka08

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

Sanjeev Kumar


this what it says


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sanjeevka08
Starting Member

India
18 Posts

Posted - 10/18/2012 :  01:07:32  Show Profile  Reply with Quote
Thanks....

Sanjeev Kumar
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2012 :  12:57:00  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000