在 MSSQL 2008 R2 中没有聚合函数的 Pivot
本教程将介绍在 MSSQL 2008 R2 中没有聚合函数的 Pivot的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。
问题描述
这是我的 MSSQL 2008 [ERROR CODE] 表的一部分,我想将其转换为以下结构.我尝试搜索解决方法,但找不到完成任务的解决方案.我认为使用 Pivot 是不可行的,因为我不能使用聚合函数.有人可以帮助我怎么使这成为可能吗?
+----------+-------+---------------------------------------------------+
| SKILL ID | SKILL |PARAMETER|
+----------+-------+---------------------------------------------------+
| 1 | 121| STANDARD VERBIAGE & PROCEDURES |
| 1 | 121| ISSUE IDENTIFICATION|
| 1 | 121| CALL COURTESY |
| 1 | 121| ISSUE RESOLUTION |
| 2 | BO | COLLECTION PROCESS ADHERENCE |
| 2 | BO | INTELLIGENCE PARAMETER |
| 3 | EM | SOFT SKILLS|
| 3 | EM | PRODUCT KNOWLEDGE|
| 3 | EM | CALL CLOSING |
| 3 | EM | CALL OPENING |
| 4 | FLC| RESOLUTION |
| 4 | FLC| NONE |
| 5 | FTA| OTHERS |
| 5 | FTA| HYGIENE FACTORS |
| 5 | FTA| ACCOUNT SCREEN|
| 5 | FTA|ORDER , DOCUMENTATION AND CONFIGURATION|
| 5 | FTA| VALIDATION SCREEN|
| 5 | FTA| PARTY SCREEN |
| 5 | FTA| ORDER , DOCUMENTATION AND CONFIGURATION |
| 6 | NCE| COMPLIANCE |
| 6 | NCE| CRM |
| 6 | NCE| ACCOUNT LEVEL /INSTALLATION DETAILS CONFIRTMATION |
| 6 | NCE| CONTENTS/BILL DETAILS |
| 6 | NCE| SELFCARE|
| 6 | NCE| FEEDBACK/SATISFACTION |
| 6 | NCE| OBJECTION RESOLUTION|
| 6 | NCE| CUSTOMER HANDLING|
| 6 | NCE| RED ALERT |
| 7 | RTO| ZERO TOLERANCE|
| 7 | RTO| OVERALL IMPRESSION |
| 7 | RTO| SUMMARY AND CLOSING |
| 7 | RTO| PROCESS KNOWLEDGE|
| 7 | RTO| OPENING |
| 8 | SHMNP | SKILL AREA |
| 8 | SHMNP | CONVINCING SKILLS|
+----------+-------+---------------------------------------------------+
这是可能的预期输出
+-------+--------------------------------+------------------------+---------------------------------------------------+
| SKILL | PARAMETER1 | PARAMETER2 | PARAMETER3|
+-------+--------------------------------+------------------------+---------------------------------------------------+
| 121| STANDARD VERBIAGE & PROCEDURES | ISSUE IDENTIFICATION| CALL COURTESY |
| BO | COLLECTION PROCESS ADHERENCE| INTELLIGENCE PARAMETER | NULL |
| EM | SOFT SKILLS | PRODUCT KNOWLEDGE| CALL CLOSING |
| FLC| RESOLUTION| NONE | NULL |
| FTA| OTHERS | HYGIENE FACTORS | ACCOUNT SCREEN|
| NCE| COMPLIANCE| CRM | ACCOUNT LEVEL /INSTALLATION DETAILS CONFIRTMATION |
| RTO| ZERO TOLERANCE | OVERALL IMPRESSION | SUMMARY AND CLOSING |
| SHMNP | SKILL AREA| CONVINCING SKILLS| NULL |
+-------+--------------------------------+------------------------+---------------------------------------------------+
推荐答案
你可以使用 PIVOT 函数来获取结果,你只需要使用 row_number()
来帮助.
对此的基本查询将是:
select skill_id, skill, parameter,
row_number() over(partition by skill, skill_id order by skill_id) rn
from yt;
请参阅 SQL Fiddle with Demo.我使用 row_number()
将不同的值应用于 skill
和 skill_id
中的每一行,然后请问使用此行号值作为列到 PIVOT.
应用了 PIVOT 的完整代码将是:
select skill_id, skill,[Parameter_1], [Parameter_2], [Parameter_3]
from
(
select skill_id, skill, parameter,
'Parameter_'+cast(row_number() over(partition by skill, skill_id
order by skill_id) as varchar(10)) rn
from yt
) d
pivot
(
max(parameter)
for rn in ([Parameter_1], [Parameter_2], [Parameter_3])
) piv;
请参阅 SQL Fiddle with Demo.
在您的情况下,似乎每个技能都有未知数量的参数.如果是这样,那么请问需要使用动态 SQL 来获取结果:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Parameter_'
+cast(row_number() over(partition by skill, skill_id
order by skill_id) as varchar(10)))
from yt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT skill_id, skill,' + @cols + ' from
(
select skill_id, skill, parameter,
''Parameter_''+cast(row_number() over(partition by skill, skill_id
order by skill_id) as varchar(10)) rn
from yt
) x
pivot
(
max(parameter)
for rn in (' + @cols + ')
) p '
execute(@query);
请参阅 SQL Fiddle with Demo
好了关于在 MSSQL 2008 R2 中没有聚合函数的 Pivot的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。