Scott Yokiel MOSS 2007 & .NET blog (and some bad spelling)

Thursday, March 20, 2008

Here is a simple example of a pivot function in SQL 2005

CREATE TABLE #UserTime

(

UserName VARCHAR(50),

Class VARCHAR(50),

Week_Date DATETIME,

Hours DECIMAL(5,2),

CategoryName VARCHAR(50),

IsBillable BIT,

CategoryAppr VARCHAR(5)

)

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-17',40,'Client Billable', 1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',16.00 ,'Administration' , 0,'AD'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',3.00,'Client Billable' ,1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',21.00,'PTO' ,0,'PTO'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-02',40.00,'Client Billable' ,1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-09',36.00 ,'Client Billable',1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-09',4.00 ,'PTO',0,'PTO'

Select * from #UserTime

--SELECT Final.UserName,Final.Week_Date, SUM(Final.CB) ,SUM(Final.AD),SUM(Final.PTO)

--from

--(SELECT UserName,Week_Date, [CB] as CB, [AD] as AD, [PTO] as PTO

-- FROM #UserTime AS P

-- PIVOT

-- (

-- Sum(P.Hours)

-- FOR P.CategoryAppr

-- IN([CB], [AD], [PTO])

-- ) AS Pivoted

--) as Final

--Group By Final.UserName,Final.Week_Date

SELECT UserName, Week_Date, SUM([0]) as NonBillable, SUM([1]) as Billable

FROM #UserTime AS P

PIVOT

(Sum(P.Hours)

FOR P.IsBillable

IN([0], [1])

) AS Pivoted

Group By UserName, Week_Date

DROP TABLE #UserTime

3/20/2008 3:01:59 PM (Central Standard Time, UTC-06:00)

Comments [0] | Trackback

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):