J Livingston SQL (4/5/2014)
this may help you on your way....
Yesterday I successfully tested this solution and used it in my production script (much longer). It works perfectly. I tested by my own the 'FOR XML PATH' clause too without success, but your idea of multialiased temp table are the key. I imprecisely post my query outputs, so you only mistakenly understand what I needed concatenate and what not. Below I post my tested version with this needed concatenation. Thank you J Livingston VERY MUCH!
USE [tempdb]
GO
SELECT s_element.sendeplatz,
jingle.sztitle,
arolle.szname AS ptype,
aperson.szname AS pname,
publisher.szname AS pub,
jingle.lid AS JID,
jingle.szshortinfo
INTO #tmp
FROM s_element
INNER JOIN s_planhdh ON s_element.planheaderid = s_planhdh.planheaderid
INNER JOIN jingle ON s_element.lprogrammitemid = jingle.lid
INNER JOIN jinglepersonrolle ON jingle.lid = jinglepersonrolle.ljingleid
INNER JOIN arolle ON jinglepersonrolle.larolleid = arolle.lid
INNER JOIN publisher ON jingle.lid = publisher.lid
INNER JOIN aperson ON jinglepersonrolle.lapersonid = aperson.lid
WHERE (s_planhdh.filetype = 'I') AND (s_element.lprogrammitemtypid = 30)
-- SELECT * FROM #tmp
SELECT
sendeplatz,
sztitle,
LTRIM(RTRIM(ISNULL(STUFF((
SELECT ', ' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Author'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' '), ''))),
LTRIM(RTRIM(ISNULL(STUFF((
SELECT ', ' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Composer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' '), ''))),
LTRIM(RTRIM(ISNULL(STUFF((
SELECT ', ' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Performer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' '), ''))),
LTRIM(RTRIM(ISNULL(STUFF((
SELECT ', ' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Arrangeur'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' '), ''))),
pub,
JID,
szshortinfo
FROM #tmp p1
GROUP BY sendeplatz,sztitle,pub,JID,szshortinfo
DROP TABLE [dbo].[#tmp]
GO