Pivot or similiar

March 2, 2023

I have 5 countries that all have been assigned with a risk score.
I want to bring the vertically displayed countries & risk scores to display horizontally with the risk scores assigned to the respective countries but fail to do the pivot right.

I tried a pivot :

SELECT * FROM
(
SELECT 
p.[QandA Key],p.[Form Submission Date],p.[FSP Number],p.[Answer],p.[AML_Risk_Score] 
FROM [dbo].[v_QandA_AMLRiskScore] p
INNER JOIN [dbo].[v_QandA_AMLRiskScore] c
on p.[QandA Key] = c.[QandA Key]
WHERE p.[Question Code] in ('AMLCFTQ0260','AMLCFTQ0261','AMLCFTQ0262','AMLCFTQ0263','AMLCFTQ0264')
) t
PIVOT(
SUM([AML_Risk_Score] )
FOR [Answer] 
in( 
 [Country 1]
,[Country 2]
,[Country 3]
,[Country 4]
,[Country 5]
)
)
AS pivot_table`

This one does not provide the riskscores, as they are all NULL.

Then I tried this, with differnt effect but not successfull either:

SELECT 
[FSP Number],[Form Submission Date]
,CASE WHEN [Question Code]='AMLCFTQ0260'THEN [AML_Risk_Score] END AS [Country1_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0261'THEN [AML_Risk_Score] END AS [Country2_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0262'THEN [AML_Risk_Score] END AS [Country3_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0263'THEN [AML_Risk_Score] END AS [Country4_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0264'THEN [AML_Risk_Score] END AS [Country5_Risk]
FROM [dbo].[v_QandA_AMLRiskScore]
WHERE [Question Code] in ('AMLCFTQ0260','AMLCFTQ0261','AMLCFTQ0262','AMLCFTQ0263','AMLCFTQ0264')

This results in this:

what it should look like is:

How does the code need to look like?

@siggemannen Now I tried this:

SELECT 
[FSP Number],[Form Submission Date]
,CASE WHEN [Question Code]='AMLCFTQ0260'THEN SUM([AML_Risk_Score])  END AS [Country1_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0261'THEN SUM([AML_Risk_Score])  END AS [Country2_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0262'THEN SUM([AML_Risk_Score])  END AS [Country3_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0263'THEN SUM([AML_Risk_Score])  END AS [Country4_Risk]
,CASE WHEN [Question Code]='AMLCFTQ0264'THEN SUM([AML_Risk_Score])  END AS [Country5_Risk]
FROM [dbo].[v_QandA_AMLRiskScore]
WHERE [Question Code] in ('AMLCFTQ0260','AMLCFTQ0261','AMLCFTQ0262','AMLCFTQ0263','AMLCFTQ0264') 
and [FSP Number] = 'FSP7442' and [Form Submission Date]= '2021-07-01'
group by [FSP Number],[Form Submission Date],[Question Code]

But still get this:

How do I bring it down to one line?

>Solution :

Something like this might work better:

SELECT 
[FSP Number],[Form Submission Date]
,SUM(CASE WHEN [Question Code]='AMLCFTQ0260'THEN [AML_Risk_Score] END) AS [Country1_Risk]
,SUM(CASE WHEN [Question Code]='AMLCFTQ0261'THEN [AML_Risk_Score] END) AS [Country2_Risk]
,SUM(CASE WHEN [Question Code]='AMLCFTQ0262'THEN [AML_Risk_Score] END) AS [Country3_Risk]
,SUM(CASE WHEN [Question Code]='AMLCFTQ0263'THEN [AML_Risk_Score] END) AS [Country4_Risk]
,SUM(CASE WHEN [Question Code]='AMLCFTQ0264'THEN [AML_Risk_Score] END) AS [Country5_Risk]
FROM    [dbo].[v_QandA_AMLRiskScore]
WHERE   [Question Code] in ('AMLCFTQ0260','AMLCFTQ0261','AMLCFTQ0262','AMLCFTQ0263','AMLCFTQ0264') 
and [FSP Number] = 'FSP7442' and [Form Submission Date]= '2021-07-01'
group by [FSP Number],[Form Submission Date]