The Row Order Positions and Dynamic CrossTab

• erdem1973

  SSCommitted

  Points: 1843

  More actions

  June 11, 2011 at 6:50 am

  #1337373

  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..

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 13, 2011 at 8:15 pm

  #1338139

  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