April 4, 2014 at 12:32 pm
Sean Lange (4/4/2014)
domingo.sqlservercentral (4/4/2014)
... and? Please...Well you gave us a bunch of data and said "here is my results". Results of what? Presumably a query.
The problem is that you have the same column name in a number of these tables and there is nothing indicate the relation between these tables. How about if you post the query that you used and explain what you want as output?
This is a two way street. When you provide details about what you want we can help you figure out a way to get them. Otherwise we are just guessing.
Sean...I think the query is in the original post,,
SELECT
s_element.sendeplatz,
RTRIM (ISNULL(jingle.SzTitle, '')),
ISNULL (auths.szname, ''),
ISNULL (comps.szname, ''),
ISNULL (performs.szname, ''),
ISNULL (arrangs.szname, ''),
ISNULL (publisher.szname, ''),
RTRIM (ISNULL(jingle.lid, '')),
RTRIM (ISNULL(jingle.szshortinfo, ''))
FROM s_planhdh
LEFT OUTER JOIN s_element ON s_element.planheaderid = s_planhdh.planheaderid
LEFT OUTER JOIN programmitem ON programmitem.lid = s_element.lprogrammitemid
LEFT OUTER JOIN jingle ON jingle.lid = s_element.lprogrammitemid
LEFT OUTER JOIN publisher ON publisher.lid = jingle.lpublisherid
LEFT OUTER JOIN jinglepersonrolle AS authors ON authors.ljingleid = jingle.lid AND authors.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Author')
LEFT OUTER JOIN jinglepersonrolle AS composers ON composers.ljingleid = jingle.lid AND composers.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Composer')
LEFT OUTER JOIN jinglepersonrolle AS arrangeurs ON arrangeurs.ljingleid = jingle.lid AND arrangeurs.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Arrangeur')
LEFT OUTER JOIN jinglepersonrolle AS performers ON performers.ljingleid = jingle.lid AND performers.larolleid = (SELECT lid FROM arolle WHERE arolle.szname = 'Performer')
LEFT OUTER JOIN aperson AS comps ON comps.lid = composers.lapersonid
LEFT OUTER JOIN aperson AS auths ON auths.lid = authors.lapersonid
LEFT OUTER JOIN aperson AS arrangs ON arrangs.lid = arrangeurs.lapersonid
LEFT OUTER JOIN aperson AS performs ON performs.lid = performers.lapersonid
WHERE
lprogrammitemtypid=30
AND filetype='I'
ORDER BY s_element.sendeplatz
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 4, 2014 at 12:39 pm
Ah so it is. My bad. So...
Can you explain the output you posted? It is all text on screen so there is no separation of columns. Is this a comma separated list for each SzTitle and date maybe???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2014 at 2:44 pm
domingo.sqlservercentral (3/26/2014)
... and I need this rows concatenated as below:2014-03-25 12:31:00.000StationIDAnnie LennoxDave StewartEurythmicsDave StewartEMI1IDshortinfo
2014-03-25 13:33:00.000TrafficBob Dylan, Louis ArmstrongPaul McCartney, Bob DylanGlenn Miller, Pet Shop BoysWarner2TRAFFICshortinfo
2014-03-25 14:38:00.000CarpetPaul McCartney, Bob Dylan, Mike RutherfordPaul McCartney, Bob Dylan, Mike RutherfordQueen, The Beatles, GenesisFreddie MercuryUniversal3CARPETshortinfo
2014-03-25 15:41:00.000StationIDAnnie LennoxDave StewartEurythmicsDave StewartEMI1IDshortinfo
2014-03-25 16:45:00.000TrafficBob Dylan, Louis ArmstrongPaul McCartney, Bob DylanGlenn Miller, Pet Shop BoysWarner2TRAFFICshortinfo
am interested in what you intend to do with your output above...I don't see any determining column to distinguish authors from composers from performers etc....???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 5, 2014 at 6:18 am
this may help you on your way....
USE [tempdb]
GO
-- reconfigured your original query...and removed the subeselects and aliases
-- hopefully you can follow structure
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
--is this what you are looking for below ??
SELECT
sendeplatz,
sztitle,
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Author'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Composer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
STUFF((
SELECT ',' + rtrim(pname)
FROM #tmp p2
WHERE p1.sendeplatz = p2.sendeplatz
AND ptype = 'Performer'
ORDER BY p2.pname
FOR XML PATH('')
), 1, 1, ' ')
+','+
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
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 9, 2014 at 3:51 am
Sean Lange (4/4/2014)
Ah so it is. My bad. So...Can you explain the output you posted? It is all text on screen so there is no separation of columns. Is this a comma separated list for each SzTitle and date maybe???
I'm sorry. In fact, I posted that outputs without visible separators, they was removed on this page (result was tab separated). But, in the needed result I wrote, needed separation is visible by the space and the comma locations (comma means concatenated data, and space means separation - next column).
April 9, 2014 at 4:04 am
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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply