asked on

MS Sql statement combine two tables

Hello All,

I need assistance with a sp to combine the results from two tables into one. I'm building a FAQ script and need to join the answers with the question.

If i use this
SELECT faqs.faqid, faqs.question, answers.answer FROM faqs RIGHT OUTER JOIN answers ON faqs.faqid = answers.faqid

faqid question answer
------- ------------ -------------
1 question1 anwser1
1 question1 anwser2
2 question1 anwser1

it returns multiple rows for each question answer.

What I need returned is this

faqid question answer answer2
------- ------------ ------------- -------------
1 question1 answer1 answer2

Thanks

Ephraim Wangoya

SELECT faqid, question,
Stuff((Select ',' + answer
From faqs T2
Where T1.faqid = T2.faqid
For xml Path('')
), 1, 1, '') as Answers
From faqs T1
Group By faqid, question

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

cyberkiwi

You can replace "order by (select 1)" with"order by answers.number" or "order by answers.id" as required

GrantBailey

ASKER

cyberkiwi,

that returned "column 'X.faqid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

My thought was I needed to create a temp table to but the results in.

cyberkiwi

Can you paste the exact query you used?

cyberkiwi

Without checking column types or putting any data, the query as presented was correct as this shows

create table faqs (faqid int, question int)
create table answers (faqid int, answer int)

SELECT faqid, question,
    max(case when rn=1 then answer end) Answer1,
    max(case when rn=2 then answer end) Answer2,
    max(case when rn=3 then answer end) Answer3,
    max(case when rn=4 then answer end) Answer4  -- etc
FROM
(
    SELECT  faqs.faqid, faqs.question, answers.answer, rn=row_number() over (partition by faqs.faqid order by (select 1))
    FROM  faqs
    LEFT OUTER JOIN answers ON faqs.faqid = answers.faqid
) X
group by faqid, question

Open in new window

No errors.

GrantBailey

ASKER

Thanks cyberkiwi