Query to select from two tables with linear output 1

[TysonLPrice]

I'm looking at another forum post where the OP wants to list the contents from two tables in a linear fashion. The only response so far is the application should handle it and not do it in a query. I agree with that but thought I would try it myself anyway. I'm not getting anywhere on it. Anybody see a straightforward solution?

These are the tables:

create table #Section(SectionID int, sectionname varchar(30))
create table #Question(sectionid int, QuestionID int, Question varchar(30))

Here is the data:

insert into #section(sectionid, sectionname) values(1,'Math')
insert into #Question(sectionid,questionid,question) values(1,1,'what is 1 + 1')
insert into #Question(sectionid,questionid,question) values(1,2,'what is 2 + 2')

insert into #section(sectionid, sectionname) values(2,'History')
insert into #Question(sectionid,questionid,question) values(2,3,'Who is buried in Grant''s tomb')
insert into #Question(sectionid,questionid,question) values(2,4,'How long was the 100 days war')

This is the desired output:

Math
what is 1 + 1
what is 2 + 2
History
Who is buried in Grant's tomb
How long was the 100 days war

In other words join the subject to the questions but list the subject first followed by the questions.

 

[imex]

Hi,

Try:

select SectionID, 0 as QuestionID, sectionname
from @Section

union all

select SectionID, QuestionID, Question
from @Question

order by SectionID, QuestionID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[TysonLPrice]

Thanks for responding imex. I've been looking and over and partition but I think that is the wrong approach. What you provided was not the desired output but it got me thinking. This seems to work:

create table #temp(sectionid int, questionid int, text varchar(100))

insert into #temp
select SectionID, 0 as QuestionID, sectionname 'text'
from #Section

union all

select SectionID, QuestionID, Question 'text'
from #Question

order by SectionID, QuestionID 

select text from #temp