How can I add a whole column of data as a row?

Question

I am dealing with a library database today. The structure is kind of odd, and I am having trouble pulling the data how I want it to appear.

I have this query:

SELECT 
    lc.catalogID, hb_g.intro AS 'Genre/Subject', gk.kidsAge AS 'Ages', 
    pb_g.intro AS 'Genre/Subject', pb.ageRange AS 'Ages'
FROM 
    library.libraryCatalog lc
INNER JOIN 
    library.hardbacks hb ON lc.catalogID = hb.catalogId
INNER JOIN 
    library.paperbacks pb ON lc.catalogID = pb.catalogId
LEFT JOIN 
    library.genres hb_g ON hb.genreId = hb_g.genreId 
LEFT JOIN 
    library.genres pb_g ON pb.genreId = pb_g.genreId
LEFT JOIN 
    library.bookSeries bs ON hb.id = bs.logId
LEFT JOIN 
    library.genreKids gk ON bs.kidsId = gk.kidsId
WHERE 
    lc.libraryID = 87

It produces results shown below. The issue I have is that I need the Fairy Tales and 12+ result to appear in the same columns as the other genres.

catalogID   Genre/Subject   Age up to   Genre/Subject   Ages
--------------------------------------------------------------
2021        Mystery         8+          Fairy Tales     12+
2021        Sci-Fi/Fantasy  12+         Fairy Tales     12+
2021        Fiction         10+         Fairy Tales     12+
2021        Non-Fiction     12+         Fairy Tales     12+
2021        Biography       16+         Fairy Tales     12+
2021        Historical      10+         Fairy Tales     12+

I am hoping for something like this:

catalogID   Genre/Subject   Age up to   
------------------------------------------
2021        Mystery         8+          
2021        Sci-Fi/Fantasy  12+         
2021        Fiction         10+         
2021        Non-Fiction     12+         
2021        Biography       16+         
2021        Historical      12+         
2021        Fairy Tales     12+  <---- moved here

I tried using ISNULL and COALESCE but neither of those worked.

Is something like this possible?

Answer 1

It looks like you need to union two separate resultsets, then join back on catalogID.

SELECT 
    lc.catalogID,
    b.intro AS GenreOrSubject,
    b.Ages
FROM 
    library.libraryCatalog lc
JOIN (
    SELECT
        hb.catalogId,
        hb.genreId,
        gk.kidsAge AS Ages
    FROM
        library.hardbacks hb
    JOIN 
        library.bookSeries bs ON hb.id = bs.logId
    JOIN 
        library.genreKids gk ON bs.kidsId = gk.kidsId

    UNION ALL

    SELECT
        pb.catalogID,
        pb.genreId,
        pb.ageRange
    FROM
        library.paperbacks pb
) b
    ON lc.catalogID = b.catalogId
JOIN 
    library.genres b_g ON b.genreId = b_g.genreId 
WHERE
    lc.libraryID = 87;