not very elegant, but try this:
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)