June 16, 2009 at 1:11 am
Hi,
I have temp table which has set of Records.I need to pivot the table based on the WeekUID.Please see the below query.
---------------------------------------------------
-------Temp Table Created-----------------
/*This is the temp table*/
CREATE TABLE #RESULT
(
EngagementUID INT,
EngagementID VARCHAR(50),
EngagementName varchar(150),
EngagementPartner varchar(200),
EngagementManager varchar(200),
WEEKUID INT,
SCHDuration DECIMAL(18,3),
ActDuration DECIMAL(18,3)
)
/*Inserting into temp table based on certain conditions*/
INSERT INTO #RESULT
SELECT DISTINCT
ENG.*,
ISNULL(BDACTSCH.WEEKUID,0),
ISNULL(BDACTSCH.ActulaDuration,0) AS SchDuration,
ISNULL(BDACTSCH.SchDuration,0) AS ActDuration
FROM #Engagement ENG
INNER JOIN #BUDACTSCH BDACTSCH ON
BDACTSCH.EngagementUID=ENG.EngagementUID
/*Pivoting part based on the WeekUID*/
SELECT EngagementUID,
EngagementName,
EngagementPartner,
EngagementManager,
COALESCE([1],0) AS FirstWeek,
COALESCE([2],0) AS SecondWeek,
COALESCE([3],0) AS ThirdWeek,
COALESCE([4],0) AS FourthWeek
FROM
(
SELECT
EngagementUID,
EngagementName,
EngagementPartner,
EngagementManager,
WeekUID,
SCHDuration,
ActDuration
FROM #RESULT)R
PIVOT
(
SUM(SCHDuration) --SUM(ActDuration)
FOR WeekUID IN ([1],[2],[3],[4])
)AS PVT
Note--Here i have used the aggregate function SUM(SCHDuration) which works fine ie.it shows the duration for 1,2,3 and 4th week.Similarly i need to get the ActDuration for the corresponding weeks in the above query itself.Is it possible, i tried but it's showing error.Can anyone please help on this.
June 16, 2009 at 2:19 am
Hi,
please have a look at the following post http://www.sqlservercentral.com/Forums/FindPost714447.aspx.
I used two CTEs for the PIVOT.
If you need further help please provide sample data as described in the link in my signature.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply