How can I correct this mysql join query and put 1 to many in the same row?

Question

This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to achieve that. Appreciate any advice!

    SELECT google_responsive_headlines.responsive_headlines,
        google_responsive_headlines.responsive_path_1,
        google_responsive_headlines.responsive_path_2,
        d.responsive_descriptions,
        s.clicks,
        s.cost,
        s.impressions,
        s.ad_id,
        s.status,
        s.final_url,
        s.display_url
    FROM
        irene_db.google_responsive_descriptions d
            INNER JOIN
                (SELECT 
                    SUM(clicks) AS clicks,
                        SUM(cost) AS cost,
                        SUM(impressions) AS impressions,
                        google_text_ads.ad_id,
                        google_text_ads.status,
                        google_text_ads.final_url,
                        google_text_ads.display_url
                FROM
                    google_text_ads
                WHERE
                    google_text_ads.customer_id = 144
                        AND (google_text_ads.date >= '2022-05-21'
                        AND google_text_ads.date <= '2022-06-21')
                        AND google_text_ads.status IN ('ENABLED')
                        AND google_text_ads.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
                GROUP BY ad_id) s ON s.ad_id = d.ad_id
        INNER JOIN
        irene_db.google_responsive_headlines ON s.ad_id = google_responsive_headlines.ad_id
            AND google_responsive_headlines.customer_id = 144
            AND (google_responsive_headlines.date >= '2022-05-21'
            AND google_responsive_headlines.date <= '2022-06-21')
            group by google_responsive_headlines.responsive_headlines
    LIMIT 50;

Here's my results...

How can I get responsive_headlines to be in separate columns and have the query return 1 row?

EDIT: The query result is the same in every other column except responsive_headline. There can be up to 15 of those per 1 row in google_text_ads. So is there anyway in my return query I can have responsive_headline1, responsive_headline2,... responsive_headline15.

    responsive_headlines    responsive_path_1   responsive_path_2   responsive_descriptions clicks  cost    impressions ad_id   status  final_url   display_url customer_id date    type
    Retirement Plan Administration, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
    We Help Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
    We Work W/ Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD

Answer 1

You can first take a row-number, then conditionally aggregate (pivot) over that.

The joins and grouping/partitioning columns are unclear, you may need to modify.

SELECT
    grh.*
    d.responsive_descriptions,
    s.clicks,
    s.cost,
    s.impressions,
    s.ad_id,
    s.status,
    s.final_url,
    s.display_url
FROM
    irene_db.google_responsive_descriptions d
INNER JOIN (
    SELECT 
        SUM(gta.clicks) AS clicks,
        SUM(gta.cost) AS cost,
        SUM(gta.impressions) AS impressions,
        gta.ad_id,
        gta.status,
        gta.final_url,
        gta.display_url
    FROM
        google_text_ads gta
    WHERE gta.customer_id = 144
      AND gta.date >= '2022-05-21'
      AND gta.date <= '2022-06-21'
      AND gta.status IN ('ENABLED')
      AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
    GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
    SELECT
        grh.adid,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions,
        MAX(CASE WHEN grh.rn = 1  THEN grh.responsive_headlines END) responsive_headlines1,
        MAX(CASE WHEN grh.rn = 2  THEN grh.responsive_headlines END) responsive_headlines2,
        MAX(CASE WHEN grh.rn = 3  THEN grh.responsive_headlines END) responsive_headlines3,
        MAX(CASE WHEN grh.rn = 4  THEN grh.responsive_headlines END) responsive_headlines4,
        MAX(CASE WHEN grh.rn = 5  THEN grh.responsive_headlines END) responsive_headlines5,
        MAX(CASE WHEN grh.rn = 6  THEN grh.responsive_headlines END) responsive_headlines6,
        MAX(CASE WHEN grh.rn = 7  THEN grh.responsive_headlines END) responsive_headlines7,
        MAX(CASE WHEN grh.rn = 8  THEN grh.responsive_headlines END) responsive_headlines8,
        MAX(CASE WHEN grh.rn = 9  THEN grh.responsive_headlines END) responsive_headlines9,
        MAX(CASE WHEN grh.rn = 10 THEN grh.responsive_headlines END) responsive_headlines10,
        MAX(CASE WHEN grh.rn = 11 THEN grh.responsive_headlines END) responsive_headlines11,
        MAX(CASE WHEN grh.rn = 12 THEN grh.responsive_headlines END) responsive_headlines12,
        MAX(CASE WHEN grh.rn = 13 THEN grh.responsive_headlines END) responsive_headlines13,
        MAX(CASE WHEN grh.rn = 14 THEN grh.responsive_headlines END) responsive_headlines14,
        MAX(CASE WHEN grh.rn = 15 THEN grh.responsive_headlines END) responsive_headlines15
    FROM (
        SELECT
            grh.*,
            ROW_NUMBER() OVER (
                PARTITION BY
                  grh.ad_id,
                  grh.responsive_path_1,
                  grh.responsive_path_2,
                  grh.responsive_descriptions
                ORDER BY grh.date) rn
        FROM
            irene_db.google_responsive_headlines grh
        WHERE grh.customer_id = 144
          AND grh.date >= '2022-05-21'
          AND grh.date <= '2022-06-21'
    ) grh
    GROUP BY
        grh.responsive_headlines,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;

On older versions of MySQL, you need to aggregate them all up and use SUBSTRING_INDEX to pull out each one

SELECT
    grh.*
    d.responsive_descriptions,
    s.clicks,
    s.cost,
    s.impressions,
    s.ad_id,
    s.status,
    s.final_url,
    s.display_url
FROM
    irene_db.google_responsive_descriptions d
INNER JOIN (
    SELECT 
        SUM(gta.clicks) AS clicks,
        SUM(gta.cost) AS cost,
        SUM(gta.impressions) AS impressions,
        gta.ad_id,
        gta.status,
        gta.final_url,
        gta.display_url
    FROM
        google_text_ads gta
    WHERE gta.customer_id = 144
      AND gta.date >= '2022-05-21'
      AND gta.date <= '2022-06-21'
      AND gta.status IN ('ENABLED')
      AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
    GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
    SELECT
        grh.adid,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 1) responsive_headlines1,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 2) responsive_headlines2,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 3) responsive_headlines3,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 4) responsive_headlines4,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 5) responsive_headlines5,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 6) responsive_headlines6,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 7) responsive_headlines7,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 8) responsive_headlines8,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 9) responsive_headlines9,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 10) responsive_headlines10,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 11) responsive_headlines11,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 12) responsive_headlines12,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 13) responsive_headlines13,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 14) responsive_headlines14,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 15) responsive_headlines15
    FROM
        irene_db.google_responsive_headlines grh
    WHERE grh.customer_id = 144
      AND grh.date >= '2022-05-21'
      AND grh.date <= '2022-06-21'
    GROUP BY
        grh.responsive_headlines,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;