Jayson Jayson - 1 month ago 6
SQL Question

Converting Column Headers to Row elements

I have 2 tables I am combining and that works but I think I designed the second table wrong as I have a column for each item of what really is a multiple choice question. The query is this:

select Count(n.ID) as MemCount, u.Pay1Click, u.PayMailCC, u.PayMailCheck, u.PayPhoneACH, u.PayPhoneCC, u.PayWuFoo
from name as n inner join
UD_Demo_ORG as u on n.ID = u.ID
where n.MEMBER_TYPE like 'ORG_%' and n.CATEGORY not like '%_2' and
(u.Pay1Click = '1' or u.PayMailCC = '1' or u.PayMailCheck = '1' or u.PayPhoneACH = '1' or u.PayPhoneCC = '1' or u.PayWuFoo = '1')
group by u.Pay1Click, u.PayMailCC, u.PayMailCheck, u.PayPhoneACH, u.PayPhoneCC, u.PayWuFoo


The results come up like this:

Count Pay1Click PayMailCC PayMailCheck PayPhoneACH PayPhoneCC PayWuFoo
8 0 0 0 0 0 1
25 0 0 0 0 1 0
8 0 0 0 1 0 0
99 0 0 1 0 0 0
11 0 1 0 0 0 0


So the question is, how can I get this to 2 columns, Count and then the headers of the next 6 headers so the results look like this:

Count PaymentType
8 PayWuFoo
25 PayPhoneCC
8 PayPhoneACH
99 PayMailCheck
11 PayMailCC


Thanks.

Answer

Try this one

Select Count,

CASE WHEN Pay1Click=1 THEN 'Pay1Click'
           PayMailCC=1 THEN ' PayMailCC'
          PayMailCheck=1 THEN 'PayMailCheck'
          PayPhoneACH=1 THEN 'PayPhoneACH'
          PayPhoneCC=1 THEN 'PayPhoneCC'
          PayWuFoo=1 THEN 'PayWuFoo'
     END as PaymentType

FROM ......