对不同记录的PIVOT查询 [英] PIVOT query on Distinct records
问题描述
我有下表:
------------------------------------------------------
| Id Code percentage name name1 activity |
-----------------------------------------------------
| 1 Prashant 43.43 James James_ Running |
| 1 Prashant 70.43 Sam Sam_ Cooking |
| 1 Prashant 90.34 Lisa Lisa_ Walking |
| 1 Prashant 0.00 James James_ Stealing |
| 1 Prashant 0.00 James James_ Lacking |
| 1 Prashant 73 Sam Sam_ Cooking 1 |
------------------------------------------------------
问题是,由于MAX
功能,它忽略了列名Name1为Lacking
The problem is, due to MAX
function it is ignoring 0.00
value with Column name Name1 of Lacking
预期结果:
-------------------------------------------------------------------
Id Code James James_ Sam Sam_ Lisa Lisa_
-------------------------------------------------------------------
1 Prashant Running 43.43 Cooking 3.43 Walking 90.34
1 Prashant Stealing 0.0 Cooking 1 73 NULL NULL
1 Prashant Lacking 0.0 NULL NULL NULL NULL
-------------------------------------------------------------------
PIVOT查询我尝试了什么:
The PIVOT Query what I tried:
DECLARE @DynamicPivotQuery NVARCHAR(MAX)
SET @DynamicPivotQuery = N'SELECT Id,Code,James,James_,Sam,Sam_,Lisa,Lisa_
INTO ##TempPivot
FROM A
PIVOT(MAX(activity)
FOR name IN (James,Sam,Lisa)) AS PVTTable PIVOT
(
MAX(percentage)
FOR name1 IN (James_,Sam_,Lisa_)) AS PVTTable1'
EXECUTE(@DynamicPivotQuery)
SELECT *
INTO #RESULT
FROM ##TempPivot
SELECT *
FROM #RESULT
示例查询以生成数据:
CREATE TABLE A
(
Id NVARCHAR(10),
Code NVARCHAR(MAX),
percentage NVARCHAR(MAX),
name NVARCHAR(MAX),
name1 NVARCHAR(MAX),
activity NVARCHAR(MAX)
)
INSERT INTO A VALUES (1,'Prashant',43.43,'James','James_','Running')
INSERT INTO A VALUES (1,'Prashant',3.43,'Sam','Sam_','Cooking')
INSERT INTO A VALUES (1,'Prashant',90.34,'Lisa','Lisa_','Walking')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Stealing')
INSERT INTO A VALUES (1,'Prashant',0.00,'James','James_','Lacking')
INSERT INTO A VALUES (1,'Prashant',73,'Sam','Sam_','Cooking 1')
推荐答案
如果将ROW_NUMBER()添加到混合中,则数据透视表将能够保留活动和百分比之间的关联.
If you add a ROW_NUMBER() into the mix, your pivot will be able to retain the association between activities and percentages.
;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
返回:
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表转换为此:
The idea is, in thefirst common table expression, you transform the A table to this:
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列仅用于将百分比值绑定到活动.
And throughout the remaining query, the ROWNUM column just acts to bind the percentage value to the activity.
一旦您有一个有效的查询,将其动态化是很容易的.只需将所有动态部分(在这种情况下为逗号分隔的名称列表,对吗?)替换为变量即可.像这样:
Making it dynamic is easy once you have a working query. Just replace all the dynamic parts (in this case, comma-delimited lists of names, right?) with variables. Something like this:
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
这篇关于对不同记录的PIVOT查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!