Trouble concatenating rows into string with multiple joins

Question

I am struggling to create a statement in a snowflake schema. I need to show a list of all products and associated tracks but also include on each line a list of songwriters names and ownership percentages eg. add one column that looks like: "Sam Smith (50.00%), Sally Simpson (25.00%), John Chan (25.00%)". My tables are:

Table: PRODUCT -PRODUCT_ID -ALBUM_ARTIST -ALBUM_TITLE

Table: SONGWRITER -SONGWRITER_ID -FIRSTNAME -LASTNAME

Table: SONG_SONGWRITER -SONGWRITER_ID -TRACK_ID -OWNERSHIP_SHARE

Table: TRACK -TRACK_ID -PRODUCT_ID -TRACK_ARTIST |TRACK_NAME

I know that I need to concatenate (and join) the SONG_SONGWRITER and SONGWRITER tables, but I am very lost due to the several joins needed for to display the products and tracks.

I have tried (and failed with):

SELECT prod.*, tra.TRACK_NAME,(SELECT (SELECT ','+ writer.FIRSTNAME +' ' +writer.LASTNAME + '(' + FORMAT(ssw.OWNERSHIP_SHARE,'P2') + ')' FROM SONGWRITER writer INNER JOIN SONG_SONGWRITER ssw ON ssw.SONGWRITER_ID=writer.SONGWRITER_ID FOR XML PATH ('')))
FROM PRODUCT prod
INNER JOIN TRACK tra
ON tra.PRODUCT_ID=prod.PRODUCT_ID
ORDER BY tra.SEQUENCE;

As for the expected result it should hopeful return something like this:

PRODUCT_ID | ALBUM_ARTIST | ALBUM_TITLE | TRACK_NAME | OWNERSHIP

1 | Meatloaf | Bat out of Hell | Bat out of Hell | Meat loaf (50%), Johnny songwriter (50%

Can anyone please help?

update your question add a coherent data sample and the expected result ( as tabular text) — ScaisEdge, Nov 21 '19 at 20:28
Are you using snowflake, or sql-server? These are different products. — GMB, Nov 21 '19 at 20:29
As for the expected result it should hopeful return something like this: PRODUCT_ID | ALBUM_ARTIST | ALBUM_TITLE | TRACK_NAME | OWNERSHIP 1 | Meatloaf | Bat out of Hell | Bat out of Hell | Meat loaf (50%), Johnny songwriter (50%) — ConfusedAtITW, Nov 21 '19 at 20:35

score 0 · Answer 1 · answered Nov 21 '19 at 20:51

You can join the 4 tables and turn on aggregation. In snowflake, listagg() can be used to aggregate strings:

select 
    p.product_id
    p.album_artist,
    p.album_title,
    t.track_name,
    listagg(concat(s.firstname, ' ', s.lastname, ' (', ss.ownership_share, ')') 
        within group (order by ss.ownership_share) ownership_share
from product p
inner join track t on t.product_id = p.product_id
inner join song_songwriter ss on ss.track_id = p.track_id
inner join songwriter s on s.songwriter_id = ss.songwriter_id
group by
    p.product_id
    p.album_artist,
    p.album_title,
    t.track_name
order by p.product_id, t.sequence;

score 0 · Answer 2 · answered Nov 21 '19 at 20:53

0

Snowflake doesn't leverage the '+' notation when concatenating strings. You should leverage the concat() function or the || notation:

https://docs.snowflake.net/manuals/sql-reference/functions/concat.html

answered Nov 21 '19 at 20:53

Mike Walton

6,595
2
11
22

Trouble concatenating rows into string with multiple joins

2 Answers2