flipping data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

flipping data

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2009-01-06 : 14:33:34

I have a table named member_interview_resp:

member_id question_id response
12345 502066 2
23568 502066 3
12345 502068 2
23568 502068 4

Each question ID is associated with a question that is not saved to the database. I'd like to create a report showing each response to each question for each member having one line for each member. I don't believe a pivot table or case will work since I am not using an aggregate function. I tried the following:

select b.member_id,
c.response as "Diagnostic Dilemma (HB2)"
from ccmsdba.member_interview_resp b
join
(select member_id, response, question_id
from ccmsdba.member_interview_resp) as c
on b.member_id = c.member_id
and b.response = c.response
and c.question_id = 502068
and b.question_id = c.question_id

"Diagnostic Dilemma (HB2)" being the question associated with the question id. It returned:
Member_id Diagnostic Dilemma (HB2)
12345 2
23568 3

This is what I expected. But when I tried:
select b.member_id,
a.response as "Chronicity (HB1)",
c.response as "Diagnostic Dilemma (HB2)"
from ccmsdba.member_interview_resp b
join
(select member_id, response, question_id
from ccmsdba.member_interview_resp) as a
on a.member_id = b.member_id
and a.response = b.response
and b.question_id = 502066
and a.question_id = b.question_id
join
(select member_id, response, question_id
from ccmsdba.member_interview_resp) as c
on b.member_id = c.member_id
and b.response = c.response
and c.question_id = 502068
and b.question_id = c.question_id
and a.member_id = c.member_id
and a.question_id = c.question_id

select * from ccmsdba.member_interview_resp
where member_id in ('490744100 01', '499949693 01')
and question_id = 502068

This returned no data.

As you can probably tell, I am self taught, so any assistance will be greatly appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 14:41:09

So what should be your expected output?

lbeese
Starting Member

24 Posts

Posted - 2009-01-06 : 14:45:21

My expected output would be:

member_id Chronicity (HB1) Diagnostic Dilemma (HB2)
12345 2 2
23568 3 4

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 15:06:28

Then you just need this:

Select member_id,
max(case when question_id = 502066 then response else 0 end)as [Chronicity (HB1)],
max(case when question_id = 502068 then response else 0 end)as [Diagnostic Dilemma (HB2)]
from member_interview_resp
group by member_id
order by member_id

lbeese
Starting Member

24 Posts

Posted - 2009-01-06 : 15:11:27

Thank you very much!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 15:13:47

You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources