MSSQL 2008 R2中没有聚合功能的数据透视 [英] Pivot without aggregate function in MSSQL 2008 R2
问题描述
这是我的MSSQL 2008 [ERROR CODE]表的一部分，我希望将其转置为以下结构.我尝试搜索解决方法，但找不到解决方案来完成任务.我认为使用Pivot是不可行的，因为我无法使用聚合函数.有人可以帮我实现这一目标吗?
Here is a part of my MSSQL 2008 [ERROR CODE] table, which I want to transpose to following structure. I tried searching a workaround but could not find a solution to accomplish the task. Using Pivot I think is not feasible as I cannot use aggregate function. Can someone please help me to how to make this possible?
+----------+-------+---------------------------------------------------+
| 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()即可提供帮助.
You can use the PIVOT function to get the result, you will just have to use row_number() to help.
对此的基本查询将是:
select skill_id, skill, parameter,
row_number() over(partition by skill, skill_id order by skill_id) rn
from yt;
请参见带演示的SQL提琴.我使用row_number()将不同的值应用于skill和skill_id中的每一行，然后将使用此行号值作为PIVOT的列.
See SQL Fiddle with Demo. I use row_number() to apply a distinct value to each row within the skill and skill_id, you will then use this row number value as the column to PIVOT.
应用了PIVOT的完整代码为:
The full code with the PIVOT applied will be:
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提琴.
对于您来说，似乎每种技能的参数数量都是未知的.如果是这样，那么您将需要使用动态SQL来获取结果:
In your case, it seems like you will have an unknown number of parameters for each skill. If that is true, then you will want to use dynamic SQL to get the result:
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提琴
这篇关于MSSQL 2008 R2中没有聚合功能的数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
