June 11, 2011 at 4:47 am
Hello Experts,
I have a table named tblKPIResults.Table columns are like below:
CREATE TABLE [dbo].[tblKPIResults](
[PrimMonth] [int] NULL,
[KPIID] [int] NULL,
[CriteriaID] [char](10) NULL,
[PersonelRealID] [int] NULL,
[RoleID] [char](10) NULL,
[StoreID] [char](10) NULL,
[RegionID] [char](10) NULL,
[FinalResult] [decimal](18, 0) NULL
) ON [PRIMARY]
I have inserted some sample data to test it:
INSERT INTO tblKPIResults values(201101,3,'CR30',2011011281,'MY','0001','0001',98)
INSERT INTO tblKPIResults values(201101,3,'CR30',2011011283,'MY','0003','0001',69)
INSERT INTO tblKPIResults values(201101,3,'CR30',2011011285,'MY','0005','0001',88)
INSERT INTO tblKPIResults values(201101,3,'CR30',2011011284,'MY','0004','0001',79)
INSERT INTO tblKPIResults values(201101,3,'CR30',2011011282,'MY','0002','0001',99)
INSERT INTO tblKPIResults values(201101,3,'CR31',2011011282,'MY','0002','0001',66)
INSERT INTO tblKPIResults values(201101,3,'CR31',2011011284,'MY','0004','0001',100)
INSERT INTO tblKPIResults values(201101,3,'CR31',2011011285,'MY','0005','0001',77)
INSERT INTO tblKPIResults values(201101,3,'CR31',2011011283,'MY','0003','0001',96)
INSERT INTO tblKPIResults values(201101,3,'CR31',2011011281,'MY','0001','0001',100)
INSERT INTO tblKPIResults values(201101,3,'CR32',2011011281,'MY','0001','0001',96)
INSERT INTO tblKPIResults values(201101,3,'CR32',2011011283,'MY','0003','0001',85)
INSERT INTO tblKPIResults values(201101,3,'CR32',2011011285,'MY','0005','0001',60)
INSERT INTO tblKPIResults values(201101,3,'CR32',2011011284,'MY','0004','0001',70)
INSERT INTO tblKPIResults values(201101,3,'CR32',2011011282,'MY','0002','0001',80)
I have to apply following steps:
1. Create and ordered list of this resultset :
SELECT
* FROM dbo.
tblKPIResults
ORDER
BY PrimMonth,CriteriaID,FinalResult
desc
2. Determine the position number of each StoreID
3. and multiply it with a constant (such as 0.1)
4. create a new "dynamic" crosstab resultset such as:
PrimMonth Store CR30 CR31 CR32 GrandTotal
---------------------------------------------------------
For example for the criteria CR30 here is my ordered list:
PrimMonth KPIID CriteriaID PersonelRealID RoleID StoreID RegionID FinalResult
201101 3 CR30 2011011282 MY 0002 0001 99
201101 3 CR30 2011011281 MY 0001 0001 98
201101 3 CR30 2011011285 MY 0005 0001 88
201101 3 CR30 2011011284 MY 0004 0001 79
201101 3 CR30 2011011283 MY 0003 0001 69
My desired resultset for the criteria CR30 will be:
Store CR30
---------------
0002 1 x 99 x (0.1)
0001 2 x 98 x (0.1)
0005 3 x 88 x (0.1)
0004 4 x 79 x (0.1)
0003 5 x 69 x (0.1)
How can I solve this problem please help..
Regards
June 11, 2011 at 6:50 am
Hello Again,
I found the solution and wanted to share it with the forum members.
Here is the solution:
declare @query varchar(MAX)
declare @Query2 varchar(MAX)
declare @Query3 varchar(MAX)
;With CTE as (select distinct CriteriaID from [dbo].[tblKPIResults])
select @query2 = COALESCE(@query2 + ', ','') + QUOTENAME(RTRIM(CriteriaID)),
@query3 = COALESCE(@query3 + ',Sum(0.1*rn* ','') + QUOTENAME(RTRIM(CriteriaID))+') as ' + QUOTENAME(RTRIM(CriteriaID))
from CTE
Set @query3='Sum(0.1*rn*'+@query3
set @query='
;With CTE as (
SELECT *,ROW_NUMBER() over (Partition by CriteriaID Order by FinalResult Desc) as rn FROM dbo.tblKPIResults)
,CTE2 as (
select [StoreID],rn,'+@query2+' from CTE
PIVOT (max([FinalResult]) for CriteriaID IN ('+@query2+')) pvt)
select [StoreID],'+@query3+' from CTE2
group by [StoreID]'
exec (@query)
--0001 19.6 20.0 9.6
--0002 9.9 33.0 24.0
--0003 34.5 28.8 17.0
--0004 31.6 10.0 28.0
--0005 26.4 30.8 30.0
Now, I am trying to get the grand totals of each row as a separate table column..
June 13, 2011 at 8:15 pm
A dynamic PIVOT will work fine but to take it to the next level please consider using a Dynamic Cross Tab Query for this job. These two comprehensive articles on the topic of Cross Tabs explain how to use them as well as why they outperform PIVOT:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply