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 |
+----------+-------+---------------------------------------------------+
This is may expected output
+-------+--------------------------------+------------------------+---------------------------------------------------+
| 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 |
+-------+--------------------------------+------------------------+---------------------------------------------------+
You can use the PIVOT function to get the result, you will just have to use
row_number()
to help.The base query for this will be:
See SQL Fiddle with Demo. I use
row_number()
to apply a distinct value to each row within theskill
andskill_id
, you will then use this row number value as the column to PIVOT.The full code with the PIVOT applied will be:
See SQL Fiddle with Demo.
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:
See SQL Fiddle with Demo