CODEWITHSUNDEEP
sqlsql-serverpivot
SQLSeeker
SQLSeeker

Reputation: 77

Why is PIVOT returning multiple rows?

I am on SQL 2k12 and stumbling with the PIVOT command. My data looks like this

source data

I need the output like this

DimQuestion_y_attribute  [Q42] [Q13] [Q23]
Q14                      0.574 0.508 0.403
Q24                      0.117 0.19  0.111

The query I am using is this

    select DimQuestionNum_y_Attribute, [Q42],[Q13],[Q23]
from  [dbo].[Pearson_Coefficient_Values]
PIVOT 
(
 SUM(coeff_value) For DimQuestionNum_x_Rating IN ([Q42],[Q13],[Q23])
 ) p
 where surveyid = 1109245

The output I see is this

current output does not match requirements

What am I doing wrong?

Upvotes: 0

Views: 1812

Answers (2)

JamieD77
JamieD77

Reputation: 13949

as an alternative to PIVOT, you might want to try SUM(CASE) in some cases you will get better performance.

SELECT  DimQuestionNum_y_Attribute,
        SUM(CASE WHEN DimQuestionNum_x_Rating = 'Q42' THEN coeff_value END) [Q42],
        SUM(CASE WHEN DimQuestionNum_x_Rating = 'Q13' THEN coeff_value END) [Q13],
        SUM(CASE WHEN DimQuestionNum_x_Rating = 'Q23' THEN coeff_value END) [Q23]
FROM    [dbo].[Pearson_Coefficient_Values] 
WHERE   surveyid = 1109245
GROUP BY DimQuestionNum_y_Attribute

Upvotes: 1

Kostya
Kostya

Reputation: 1605

try this 

select DimQuestionNum_y_Attribute, [Q42],[Q13],[Q23]
from (select DimQuestionNum_y_Attribute,coeff_value, DimQuestionNum_x_Rating from   [dbo].[Pearson_Coefficient_Values]  where surveyid = 1109245) tb 
PIVOT 
(
 SUM(coeff_value) For DimQuestionNum_x_Rating IN ([Q42],[Q13],[Q23])
 ) p

Upvotes: 0

Related Questions