在 MSSQL 2008 R2 中没有聚合函数的 Pivot

本教程将介绍在 MSSQL 2008 R2 中没有聚合函数的 Pivot的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

在 MSSQL 2008 R2 中没有聚合函数的 Pivot 教程 第1张

问题描述

这是我的 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() 将不同的值应用于 skillskill_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的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。