Convert column with multiple unique rows to matrix table using Pivot

Jubaraj Pandit 0
Viewed 29

I have this table as input

Likelihood Impact Score
Very Likely Minimal​ Low
Very Likely Moderate High
Very Likely Severe​ High
Likely​ Minimal​ Low
Likely​ Moderate High
Likely​ Severe​ High
Possible​ Minimal​ Low
Possible​ Moderate Medium
Possible​ Severe​ High
Unlikely​ Minimal​ Low
Unlikely​ Moderate Low
Unlikely​ Severe​ Medium
Very Unlikely​ Minimal​ Low
Very Unlikely Moderate Low
Very Unlikely Severe Low

I was unable to get as my expectation. I am getting NULL values for Minimal and Severe column.

I ran the SQL

SELECT Likelihood, Minimal, Moderate, Severe
FROM 
(
    SELECT * FROM MYTABLE
) AS S
PIVOT (MAX(Score) for Impact in (Minimal, Moderate, Severe)) AS Pivot_Table

I need output as

Likelihood Minimal Moderate Severe
Very Likely Low High High
Likely Low High High
Possible Low Medium High
Unlikely Low Low Medium
Very Unlikely Low Low Low

Any suggestions?

pivot sql-server sql
1 Answers

The issue's down to special characters hiding in your data / meaning that the column's value isn't Minimal, but MinimalX, where X is a zero width space.

You can see this on this page - view the page's source & navigate to your sample data / look what appears after the values that are causing you issues, but doesn't appear after those values which aren't.

Zero Width Space following the Minimal value

You can also see this clearly by running this over your columns / on text copied from your columns. You'd expect values 32 (or error) and 7, but you get 63 and 8: select unicode(cast(substring('Minimal​', 8,1) as char)), len('Minimal​')

Screenshot of char code and length of the field

And if you paste your data into a suitable text editor (or DB Fiddle) which shows special characters, this is flagged up quickly:

DB Fiddle showing special characters

JohnLBevan0
Related