Can this be done in SQL 2008?

Question

Given this dataset:

SummaryID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 

can the data be PIVOTed or CUBEd or something like that:

  0 1 2 3 4 5 6 7 Q1 3 Q2 3 Q3 3 Q4 3 Q5 3 Q6 3 Q7 3 Q8 3 Q9 3 Q10 3 Q11 3 Q12 3 Q13 3 Q14 3 Q15 3 

Essentially, the top table shows how feedback data is stored for the feedback system. Many reviewers are invited to rate one reviewer on a scale of 0 to 7 for 15 questions. My example is a rather unlikely set of answers from 3 reviewers to show a template in a mapping table

The second table is how I want to display the data. Each "3" in the table represents COUNT responses for a given combination of Q # and rating. For example, for Q5, 3 reviewers rated this person as "5".

Clearly, real data will be much more dispersed.

I hope this is easy.

Thanks,

John Anderson

EDITED: here is my first attempt:

 SELECT * FROM (SELECT SummaryID, COUNT(Q1) AS Q1 FROM SummaryData WHERe SummaryID = 25 GROUP BY SummaryID) o PIVOT (COUNT(Q1) FOR Q1 IN ([0], [1],[2],[3],[4],[5],[6],[7])) p SummaryID 0 1 2 3 4 5 6 7 25 0 0 0 1 0 0 0 0 

I get 1 under 3, which is wrong, and I cannot figure out how to extrapolate the code to include all the other Qs.

Thanks again,

John Anderson

EDITED: Here is a sample dataset that I modeled in Excel to produce results that will more closely resemble reality.

 SummaryID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 25 1 4 5 7 4 2 0 2 1 0 2 2 3 5 0 25 4 1 1 5 6 7 7 4 0 1 3 7 3 1 1 25 5 3 2 1 7 7 4 0 1 6 7 3 2 7 1 

Results in:

  0 1 2 3 4 5 6 7 Q1 1 1 1 Q2 1 1 1 Q3 1 1 1 Q4 1 1 1 Q5 1 1 1 Q6 1 2 Q7 1 1 1 Q8 1 1 1 Q9 1 2 Q10 1 1 1 Q11 1 1 1 Q12 1 1 1 Q13 1 2 Q14 1 1 1 Q15 1 2 

Answer 1

First you need UNPIVOT to get the data in the format you need, and then PIVOT

Working example:

 SET NOCOUNT ON DECLARE @Feedback Table ( SummaryId INT, Q01 INT, Q02 INT, Q03 INT, Q04 INT, Q05 INT, Q06 INT, Q07 INT, Q08 INT, Q09 INT, Q10 INT, Q11 INT, Q12 INT, Q13 INT, Q14 INT, Q15 INT ) INSERT INTO @Feedback Values (25, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 2, 3) INSERT INTO @Feedback Values (25, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 2, 3) INSERT INTO @Feedback Values (25, 1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 2, 3) ;WITH ReOrderedData AS ( SELECT SummaryId, QuestionNum, Score, COUNT (*) as NumScores FROM ( SELECT SummaryId, Q01, Q02, Q03, Q04, Q05, Q06, Q07, Q08, Q09, Q10, Q11, Q12, Q13, Q14, Q15 FROM @Feedback ) p UNPIVOT (Score For QuestionNum IN (Q01, Q02, Q03, Q04, Q05, Q06, Q07, Q08, Q09, Q10, Q11, Q12, Q13, Q14, Q15)) as UnPvt GROUP BY SummaryId, QuestionNum, Score) SELECT QuestionNum, IsNull ([0], '') [0], IsNull ([1], '') [1], IsNull ([2], '') [2], IsNull ([3], '') [3], IsNull ([4], '') [4], IsNull ([5], '') [5], IsNull ([6], '') [6], IsNull ([7], '') [7] FROM ( SELECT QuestionNum, Score, NumScores FROM ReOrderedData ) As SourceTable PIVOT ( Max (NumScores) For Score IN ([0], [1], [2], [3], [4], [5], [6], [7]) ) As Pivotx 

Answer 2

not very elegant, but try the following:

 DECLARE @YourTable table(SummaryID int,Q1 int ,Q2 int ,Q3 int ,Q4 int ,Q5 int ,Q6 int ,Q7 int ,Q8 int ,Q9 int ,Q10 int ,Q11 int ,Q12 int ,Q13 int ,Q14 int ,Q15 int) INSERT INTO @YourTable VALUES(25,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4) INSERT INTO @YourTable VALUES(25,0,1,2,3,4,5,6,7,6,5,4,3,2,1,0) INSERT INTO @YourTable VALUES(25,0,1,2,3,4,5,6,7,6,5,4,3,2,1,0) INSERT INTO @YourTable VALUES(25,0,1,2,3,4,5,6,7,6,5,4,3,2,1,0) SELECT 'Q'+CONVERT(varchar(2),Question) AS Question , CASE WHEN Value0=0 THEN '' ELSE CONVERT(varchar(5),Value0) END AS Value0 , CASE WHEN Value1=0 THEN '' ELSE CONVERT(varchar(5),Value1) END AS Value1 , CASE WHEN Value2=0 THEN '' ELSE CONVERT(varchar(5),Value2) END AS Value2 , CASE WHEN Value3=0 THEN '' ELSE CONVERT(varchar(5),Value3) END AS Value3 , CASE WHEN Value4=0 THEN '' ELSE CONVERT(varchar(5),Value4) END AS Value4 , CASE WHEN Value5=0 THEN '' ELSE CONVERT(varchar(5),Value5) END AS Value5 , CASE WHEN Value6=0 THEN '' ELSE CONVERT(varchar(5),Value6) END AS Value6 , CASE WHEN Value7=0 THEN '' ELSE CONVERT(varchar(5),Value7) END AS Value7 FROM ( SELECT Question , ISNULL(SUM(Value0),0) AS Value0 , ISNULL(SUM(Value1),0) AS Value1 , ISNULL(SUM(Value2),0) AS Value2 , ISNULL(SUM(Value3),0) AS Value3 , ISNULL(SUM(Value4),0) AS Value4 , ISNULL(SUM(Value5),0) AS Value5 , ISNULL(SUM(Value6),0) AS Value6 , ISNULL(SUM(Value7),0) AS Value7 FROM ( SELECT 1 AS Question ,CASE WHEN Q1=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q1=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q1=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q1=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q1=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q1=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q1=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q1=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 2 ,CASE WHEN Q2=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q2=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q2=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q2=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q2=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q2=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q2=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q2=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 3 ,CASE WHEN Q3=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q3=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q3=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q3=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q3=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q3=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q3=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q3=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 4 ,CASE WHEN Q4=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q4=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q4=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q4=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q4=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q4=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q4=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q4=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 5 ,CASE WHEN Q5=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q5=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q5=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q5=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q5=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q5=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q5=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q5=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 6 ,CASE WHEN Q6=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q6=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q6=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q6=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q6=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q6=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q6=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q6=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 7 ,CASE WHEN Q2=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q7=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q7=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q7=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q7=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q7=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q7=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q7=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 8 ,CASE WHEN Q8=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q8=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q8=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q8=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q8=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q8=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q8=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q8=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 9 ,CASE WHEN Q9=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q9=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q9=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q9=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q9=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q9=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q9=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q9=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 10 ,CASE WHEN Q10=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q10=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q10=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q10=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q10=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q10=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q10=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q10=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 11 ,CASE WHEN Q11=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q11=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q11=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q11=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q11=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q11=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q11=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q11=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 12 ,CASE WHEN Q12=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q12=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q12=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q12=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q12=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q12=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q12=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q12=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 13 ,CASE WHEN Q13=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q13=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q13=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q13=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q13=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q13=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q13=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q13=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 14 ,CASE WHEN Q14=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q14=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q14=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q14=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q14=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q14=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q14=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q14=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable UNION ALL SELECT 15 ,CASE WHEN Q15=0 THEN 1 ELSE 0 END AS Value0 ,CASE WHEN Q15=1 THEN 1 ELSE 0 END AS Value1 ,CASE WHEN Q15=2 THEN 1 ELSE 0 END AS Value2 ,CASE WHEN Q15=3 THEN 1 ELSE 0 END AS Value3 ,CASE WHEN Q15=4 THEN 1 ELSE 0 END AS Value4 ,CASE WHEN Q15=5 THEN 1 ELSE 0 END AS Value5 ,CASE WHEN Q15=6 THEN 1 ELSE 0 END AS Value6 ,CASE WHEN Q15=7 THEN 1 ELSE 0 END AS Value7 FROM @YourTable ) dt GROUP BY Question )dt2 ORDER BY dt2.Question 

OUTPUT:

 Question Value0 Value1 Value2 Value3 Value4 Value5 Value6 Value7 -------- ------ ------ ------ ------ ------ ------ ------ ------ Q1 3 1 Q2 3 1 Q3 3 1 Q4 3 1 Q5 4 Q6 1 3 Q7 1 3 Q8 1 3 Q9 1 3 Q10 1 3 Q11 4 Q12 3 1 Q13 3 1 Q14 3 1 Q15 3 1 (15 row(s) affected)