Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Concatenating rows from aliased tables on MSSQL 2005 server Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 4:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 4:04 AM
Points: 11, Visits: 25
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

Post #1559855
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse