Hi, I have 2 tables: tbl_Questi

[mnasuto]

Hi,

I have 2 tables:
tbl_Questions with fields: Question_ID, Question
101, Question1
102, Question2
103, Question3

and
tbl_Answers with fields:Company_ID,Question_ID,Answer
1334, 101, AnswerQ1
1334, 102, AnswerQ2
1334, 103, AnswerQ3
1678, 102, AnswerQ2


Number of Questions is not constant.

I need data in the format:

Comp_ID, Question1,Question2,Question3,.....and so on
1334, AnswerQ1, AnswerQ2,AnswerQ3,................
1678, , AnswerQ2, ,...........


Is it any easy way to do it?
Please help if you can.

Best,
Marta

 

[Transcend]

How are you going to be displaying the data?

I can give you a query which will return all of the required information but the results won't 'look' like what you wanted. For this you'd create a report or spreadsheet based of the query results.

Select Company_ID, Question, Answer
from tbl_questions inner join tbl_answers on tbl_questions.question_id = tbl_Answers.question_id

This will only return questions that have answers

Transcend

 

[ClaireHCM]

I think you can use cross tab unless you have Question1 to Question 1000,otherwise should be useful.

select id,
max(case when id = id and col2 = 'AnswerQ1' then col2 else '' end) as Question1,
max(case when id = id and col2 = 'AnswerQ2' then col2 else '' end) as Question2,
max(case when id = id and col2 = 'AnswerQ3' then col2 else '' end) as Question3
from a group by id

 

[mnasuto]

Hi,

Thank you for taking time to help me.


It looks like above solutions do not apply.

Problem here is that Questions are dinammic and they are changing, depends what programme we running,we do not know which one are Question1 or Qiestion2,.....

I need hove Questions as a column names and as a value Answer .

Exp:

Comp_ID, How many PC?, Do you like Crystal?,.....
1089, 20, Yes,..............
3980, 10, No,..............


Please help if you can

Best,
M

 

[Transcend]

so the query doesn't return anything?

and you didn't answer the question i asked you?

Transcend

 

[mnasuto]

Hi Transcend,

Your Query will return

Comp_ID, Qiestion, Answer
1009, Do you like Crystal?, Yes,
1009, How many PC ?, 20
122, Do you like Crystal, No

This is not what I need.
Best,
M

 

[Transcend]

*sigh*

I'd ask you again but i'm not sure we are getting anywhere.

What are you going to be displaying your report in!??!?!

Transcend

 

[mnasuto]

Hi,
I need to have Questions as a column names and as a value Answer .

Exp:

Comp_ID, How many PC?, Do you like Crystal?,Q3,Q4.....
1089, 20, Yes,A13,A14..............
3980, 10, No,A23,A24..............

Best,
M