May 30, 2012 at 4:27 am
Dear all,
I have a table called TaskProductivity (please refer the attached image TaskProductivity_tbl.png).
It has 3 columns namely: Task, WeekStart, PercentageProductivity
Task WeekStart PercentageProductivity
Task1 19/05/2012 40
Task1 26/05/2012 100
Task2 26/05/2012 30
Task2 2/6/2012 100
Task3 2/6/2012 100
Task3 9/6/2012 100
Task3 16/6/2012 100
where I need the WeekStart Column values to be transposed as columns. (please refer the attached image TaskProductivity_tbl_outputresult.png)
Task19/05/2012 26/05/20122/6/20129/6/201216/6/2012
Task1 40 100
Task2 30 100
Task3 100 100 100
Please kindly help me in achieving this using a stored procedure.
Many thanks in advance,
Ram
May 30, 2012 at 4:30 am
Hi
You will probably be better posting the DDL's rather than attachments as people may be unwilling to open them
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 30, 2012 at 4:43 am
I take it the number of tasks and weeks can change?
If so your looking at a dynamic cross tab solution, there are 2 links in my signature by Jeff on cross tabs, part 2 goes into dynamic ones, but would recommend reading both parts.
If static (only every 3 tasks and 7 weeks) you could look at the PIVOT keyword or cross tabs again
May 30, 2012 at 8:05 am
Consider SSRS for your reports. It has built-in functionality to automatically handle these types of reports.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 31, 2012 at 12:14 am
This might help:
--Creating Table
CREATE TABLE Ex
(
[Task] [varchar](10) NULL,
[WeekStart] [date] NULL,
[PercentageProductivity] [int] NULL)
--Inserting Sample Data
Insert Into Ex
Select 'Task1', '2012/05/19', 40
Union ALL
Select 'Task1', '2012/05/26', 100
Union ALL
Select 'Task2', '2012/05/26', 30
Union ALL
Select 'Task2', '2012/06/02', 100
Union ALL
Select 'Task3', '2012/06/02', 100
Union ALL
Select 'Task3', '2012/06/09', 100
Union ALL
Select 'Task3', '2012/06/16', 100
--Query Using Static Pivot
Select Task, [2012-05-19], [2012-05-26], [2012-06-02], [2012-06-09], [2012-06-16] From Ex
Pivot
(Max(PercentageProductivity) For WeekStart IN ([2012-05-19], [2012-05-26], [2012-06-02], [2012-06-09], [2012-06-16]) ) As pvt
--Query Using Case
Select Task,
Max(Case When WeekStart = '2012-05-19' Then PercentageProductivity Else '' End) As [2012-05-19],
Max(Case When WeekStart = '2012-05-26' Then PercentageProductivity Else '' End) As [2012-05-26],
Max(Case When WeekStart = '2012-06-02' Then PercentageProductivity Else '' End) As [2012-06-02],
Max(Case When WeekStart = '2012-06-09' Then PercentageProductivity Else '' End) As [2012-06-09],
Max(Case When WeekStart = '2012-06-16' Then PercentageProductivity Else '' End) As [2012-06-16]
From Ex
Group By Task
--Query Using Dynamic Pivot
Declare @col varchar(max), @sql varchar(max)
Declare @temp table(Cols Date)
Insert Into @temp
Select Distinct WeekStart From Ex
Select @col = Coalesce(@col + ' ,', '') + QUOTENAME(Cols) From @temp
Set @sql = 'Select Task, '+@col+' From Ex
Pivot
(Max(PercentageProductivity) For WeekStart IN ('+@col+') ) As pvt'
Execute (@sql)
May 31, 2012 at 4:59 am
Use Pivot query
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply