Get all course, chapter in one column SQL Server

Question

Course

+-----+----------+
| id  | c_name   |
+-----+----------+
| 1   | course1  |
| 7   | course2  |
+-----+----------+

Chapter

+-----+----------+------------+
| id  |  Ch_name | c_id       |
+-----+----------+------------+
| 3   | Chapter1 |     1      |
| 9   | Chapter2 |     7      |
| 11  | Chapter3 |     1      |
| 17  | Chapter4 |     1      |
+-----+----------+------------+

I'm trying to select all data so that I can generate the following output:

+-----+-- |
|Course   |
+-----+-- |
|Course1  |
|Chapter1 |
|Chapter3 | 
|Chapter4 |
|         |
|Course2  |
|Chapter2 |

I have tried in this way:

select
    c.CourseID ,
    'Course' as table_name,
    c.CourseName as Course,
    '' as Chapter        
from [MstCourse]c
union 
select
    s.CourseID,
    'Chapter' as table_name,
    c.CourseName as Course,
    s.ChapterName as Chapter       
from [MstCourse] c
inner JOIN [ManageChapter] s ON c.CourseID= s.CourseID    
order by Course, Chapter

But I am not getting the results in a single column.

Answer 1

You could achieve this with a group by ... with rollup clause.

Sample data

create table course
(
  id int,
  name nvarchar(10)
);
insert into course(id, name) values
(1, 'Course1'),
(7, 'Course2');

create table chapter
(
  id int,
  name nvarchar(10),
  c_id int
);
insert into chapter(id, name, c_id) values
(3 , 'Chapter1', 1),
(9 , 'Chapter2', 7),
(11, 'Chapter3', 1),
(17, 'Chapter4', 1);

Solution

select coalesce(ch.Name, co.Name) as [Course]
from course co
join chapter ch
  on ch.c_id = co.id
group by co.Name, ch.Name with rollup
having grouping(co.Name) <> 1
order by co.Name, ch.Name;

For some background on how this solution works, have a look at this fiddle.