如果您将 ROW_NUMBER() 添加到组合中,您的数据透视表将能够保留活动和百分比之间的关联。
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,James,James_,Sam,Sam_,Lisa,Lisa_
FROM cte
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1
)
select Id, Code, MAX(James) James, MAX(James_) James_, MAX(Sam) Sam, MAX(Sam_) Sam_, MAX(Lisa) Lisa, MAX(Lisa_) Lisa_
from cte2
group by Id, Code, ROWNUM
Returns:
Id Code James James_ Sam Sam_ Lisa Lisa_
1 Prashant Running 43.43 Cooking 1 73 Walking 90.34
1 Prashant Stealing 0.00 Cooking 3.43 NULL NULL
1 Prashant Lacking 0.00 NULL NULL NULL NULL
这个想法是,在第一个公共表表达式中,将 A 表转换为:
Id Code percentage name name1 activity ROWNUM
1 Prashant 43.43 James James_ Running 1
1 Prashant 0.00 James James_ Stealing 2
1 Prashant 0.00 James James_ Lacking 3
1 Prashant 90.34 Lisa Lisa_ Walking 1
1 Prashant 73 Sam Sam_ Cooking 1 1
1 Prashant 3.43 Sam Sam_ Cooking 2
在剩下的查询中,ROWNUM 列仅用于将百分比值绑定到活动。
一旦您有了有效的查询,使其动态化就很容易了。只需将所有动态部分(在本例中为逗号分隔的名称列表,对吧?)替换为变量即可。像这样的东西:
declare @sql nvarchar(max)
declare @name_concat nvarchar(max)
declare @name1_concat nvarchar(max)
declare @select_aggs nvarchar(max)
select @name_concat = STUFF((select distinct ',' + quotename(name) from A order by 1 for xml path('')), 1, 1, '')
select @name1_concat = STUFF((select distinct ',' + quotename(name1) from A order by 1 for xml path('')), 1, 1, '')
;with cte_all_names as (
select name from A
union all
select name1 from A
)
select @select_aggs = STUFF((select distinct ',MAX(' + quotename(name) + ') ' + quotename(name) from cte_all_names order by 1 for xml path('')), 1, 1, '')
select @sql = '
;with cte as
(
select *, ROW_NUMBER() over (partition by name order by percentage desc) ROWNUM
from A
),
cte2 as
(
SELECT Id,Code,ROWNUM,' + @name_concat + ',' + @name1_concat + '
FROM cte
PIVOT(MAX(activity)
FOR name IN (' + @name_concat + ')) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (' + @name1_concat + ')) AS PVTTable1
)
select Id, Code, ' + @select_aggs + '
from cte2
group by Id, Code, ROWNUM
'
exec sp_executesql @sql