September 27, 2011 at 12:25 am
Hi
Want to write Pivot Query something like this...
SELECT PROJ_UID as ProjectUID,TASK_UID TaskUID,PROJ_NAME ProjectName,ASSN_UID AssignmentUID,TASK_NAME TaskName,
isnull(convert(char,TASK_START_DATE,6),'-NA-') as TaskStartDate,
convert(char,TASK_FINISH_DATE,6) as TaskFinishDate,ASSN_PCT_COMP TaskPercentCompleted,
CONVERT(DATETIME,'2011-09-26 00:00:00.000') as txt1
FROM
(SELECT * FROM E2E_TIMESHEET_VIEW where RES_UID='6B363656-6EFC-4A4E-AB69-C1A7FA857EAA') AS Parth
Pivot
(
COUNT(pROJECTnAME)
FOR ASSN_START_DATE in (select CONVERT(DATETIME,'2011-09-26 00:00:00.000') as txt1)
)as P
And expecting the result as below
ProjectUID TaskUID AssnUID Task_Name 26-09-2011 27-09-2011
1 1 13 abc 7 8
1 2 14 xyz 8 3
September 27, 2011 at 3:54 am
PLease anyone..!!?
September 27, 2011 at 4:22 am
Please provide the table structure and the expected output clearly
September 27, 2011 at 6:12 am
Please read the first article linked in my signature line and find out how to post to get the best answer from the forums.
We need:
1) Table scripts
2) Sample data
3) Expected output based on sample data
4) What you have tried so far
In this case, we only have 4) and half 3).
As soon as you post the rest, I'll be glad to help.
-- Gianluca Sartori
September 27, 2011 at 7:31 am
I have this Query Which works just fine..... except the sum is not working...
Declare@STR NVARCHAR(1000),@STR1 NVARCHAR(1000),@FINAL_STR NVARCHAR(4000)
SET @STR='[2011-09-26] as txt1,[2011-09-27] as txt2,[2011-09-28] as txt3,[2011-09-29] as txt4,[2011-09-30] as txt5,[2011-10-01] as txt6,[2011-10-02] as txt7'
SET @STR1='[2011-09-26],[2011-09-27],[2011-09-28], [2011-09-29],[2011-09-30], [2011-10-01], [2011-10-02]'
SET @FINAL_STR='SELECT PROJ_UID as ProjectUID,TASK_UID TaskUID,PROJ_NAME ProjectName,ASSN_UID AssignmentUID,TASK_NAME TaskName,
isnull(convert(char,TASK_START_DATE,6),''-NA-'') as TaskStartDate,
convert(char,TASK_FINISH_DATE,6) as TaskFinishDate,ASSN_PCT_COMP TaskPercentCompleted,'+@STR+'
FROM
(SELECT * FROM E2E_TIMESHEET_VIEW where RES_UID=''6B363656-6EFC-4A4E-AB69-C1A7FA857EAA'') AS Parth
Pivot
(
SUM(ASSN_ACT_WORK)
FOR ASSN_BASELINE_START_DATE in ('+@STR1+'
)
)as P'
EXEC(@FINAL_STR)
September 27, 2011 at 8:35 am
I suspect you didn't read the article, did you?
There's no way we can help without the information I requested.
If you really want us to help you, help us in the first place.
-- Gianluca Sartori
September 27, 2011 at 11:41 pm
Thanks for the Response...
There is only 1 table with the columns such as
PROJ_UIDUNIQUEIDENTIFIER
ASSN_UIDUNIQUEIDENTIFIER
RES_UIDUNIQUEIDENTIFIER
TASK_UIDUNIQUEIDENTIFIER
PROJ_NAMENVARCHAR
TASK_NAME NVARCHAR
ASSN_ACT_WORK DECIMAL
ASSN_BASELINE_START_DATEDATETIME
ASSN_BASELINE_FINISH_DATEDATETIME
ASSN_START_DATE DATETIME
ASSN_FINISH_DATE DATETIME
TASK_START_DATE DATETIME
TASK_FINISH_DATE DATETIME
ASSN_PCT_COMP DECIMAL
What i want is the data based on the ASSN_START_DATE as i am populating the Gridview for the timesheet entries.....
where i want the data as
Project Name TaskName TaskStartDate TaskFinishDate 8\1 8\2 8\3 8\4 8\5 8\6 8\7
ABC XYZ 8\1\2011 8\7\2011 2 1 2 1 5 7 3
I have got the everything now in the above mentioned table i have some assignments which means that for 1 Task I can have multiple assignments.....So basically want to SUM all the Actual work for the Assignments...
Hope this clears...
September 28, 2011 at 1:36 am
Ok, we're almost there.
Here is your code formatted as recommended in the article.
CREATE TABLE #E2E_TIMESHEET_VIEW (
PROJ_UID UNIQUEIDENTIFIER,
ASSN_UID UNIQUEIDENTIFIER,
RES_UID UNIQUEIDENTIFIER,
TASK_UID UNIQUEIDENTIFIER,
PROJ_NAME NVARCHAR,
TASK_NAME NVARCHAR,
ASSN_ACT_WORK DECIMAL,
ASSN_BASELINE_START_DATE DATETIME,
ASSN_BASELINE_FINISH_DATE DATETIME,
ASSN_START_DATE DATETIME,
ASSN_FINISH_DATE DATETIME,
TASK_START_DATE DATETIME,
TASK_FINISH_DATE DATETIME,
ASSN_PCT_COMP DECIMAL
)
DECLARE@STR NVARCHAR(1000),@STR1 NVARCHAR(1000),@FINAL_STR NVARCHAR(4000)
SET @STR= '[2011-09-26] as txt1,[2011-09-27] as txt2,[2011-09-28] as txt3,[2011-09-29] as txt4,[2011-09-30] as txt5,[2011-10-01] as txt6,[2011-10-02] as txt7'
SET @STR1='[2011-09-26],[2011-09-27],[2011-09-28], [2011-09-29],[2011-09-30], [2011-10-01], [2011-10-02]'
SET @FINAL_STR='
SELECT PROJ_UID AS ProjectUID,
TASK_UID TaskUID,
PROJ_NAME ProjectName,
ASSN_UID AssignmentUID,
TASK_NAME TaskName,
ISNULL(CONVERT(CHAR, TASK_START_DATE, 6), ''-NA-'') AS TaskStartDate,
CONVERT(CHAR, TASK_FINISH_DATE, 6) AS TaskFinishDate,
ASSN_PCT_COMP TaskPercentCompleted, '+@STR+'
FROM (SELECT *
FROM #E2E_TIMESHEET_VIEW
WHERE RES_UID = ''6B363656-6EFC-4A4E-AB69-C1A7FA857EAA''
) AS Parth
PIVOT ( SUM(ASSN_ACT_WORK) FOR ASSN_BASELINE_START_DATE IN ('+@STR1+') )as P
'
EXEC(@FINAL_STR)
We still need some sample data and the expected output based on sample data.
It's not being picky, it's just that we seriously cannot help without it.
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply