agree with Lukas; for most Database applications you would likely want to have it separated.
Still, in special scenarios I've had valid applications for something like this once in a while;
If you can live without creating separate rows for each asset_name, then this might help:
Put your asset_names into one field as a comma-separated list, and use that in emails using AMPScript's BuildRowSetFromString() function to loop through: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/buildrowsetfromstring.html
Someone solved that approach here: https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv
Which is basically:
- select + subselect on the same DE - in my example it'll be
textXMLPath, don't get confused by two the aliases de1 / de2, they're just there for the subselect to work.
- concat "," + each asset_name
- use FOR XML PATH to allow an OrderBy on Id, and place it in an xml node (we don't need the xml node)
- we now have a virtual node like
- for the created xml Node
<text></text> - replace both start and end part of the node with nothing (
- we now have an virtual list like
- Use substring to remove the spurious comma at the start of the list
note it doesn't work in query studio (query isn't accepted) - use it in automation studio where it is valid. Note that I am assuming a maxlength of 1000 for "asset_name" in the result.
SELECT ','+de1.asset_name AS text
FROM testXmlPath de1
WHERE de1.id = de2.id
ORDER BY de1.id
FOR XML PATH ('')
),'<text>',''),'</text>',''), 2, 1000) asset_name
FROM testXmlPath de2