how to get field name what are the field have value?

Question

i have one table sql server like below , from that table i want to get quesno, field name[what are the field have value]

QuesNo  A   B   C   D
1      1    0   1   0
2      0    0   0   1

Output :

QuesNo  Result
1      A,C
2      D

Is there any possible ways to get outpu?

Answer 1

This ought to do it...

SELECT QuesNo, SUBSTRING(Answers, 1, LENGTH(Answers) - 1) AS Answers
FROM (
    SELECT QuesNo,
           CASE
            WHEN A <> 0 THEN 'A,'
            ELSE ''
           END +
           CASE
            WHEN B <> 0 THEN 'B,'
            ELSE ''
           END +
           CASE
            WHEN C <> 0 THEN 'C,'
            ELSE ''
           END +
           CASE
            WHEN D <> 0 THEN 'D,'
            ELSE ''
           END AS Answers
FROM yourtable
) Foo