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

Pivot without aggregate function in MSSQL 2008 R2(在 MSSQL 2008 R2 中没有聚合函数的 Pivot)

问题描述

这是我的 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 Fiddle with Demo.我使用 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 Fiddle with Demo.

在您的情况下，似乎每个技能都有未知数量的参数.如果是这样，那么您将需要使用动态 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 Fiddle with Demo

这篇关于在 MSSQL 2008 R2 中没有聚合函数的 Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！