August 17, 2012 at 9:20 am
Hi
I have an issue which seems straightforward but it's driving me insane!
I have created a music database for my CD collection. What I am trying to do is return a track listing of an album with the writers of the tracks concatenated into one cell rartrathern on multiple rows. For example I have created a function that returns the following.
Output:
TrackNoTitleWriters
1Smells Like Teen SpiritKurt Cobain
1Smells Like Teen SpiritKrist Novoselic
1Smells Like Teen SpiritDave Grohl
2In BloomKurt Cobain
3Come as You AreKurt Cobain
4BreedKurt Cobain
5LithiumKurt Cobain
6PollyKurt Cobain
7Territorial PissingsKurt Cobain
8Drain YouKurt Cobain
9Lounge ActKurt Cobain
10Stay AwayKurt Cobain
11On a PlainKurt Cobain
12Something in the WayKurt Cobain
13Endless, NamelessKurt Cobain
13Endless, NamelessKrist Novoselic
13Endless, NamelessDave Grohl
My desired output is:
TrackNoTitleWriters
1Smells Like Teen SpiritKurt Cobain, Krist Novoselic, Dave Grohl
2In BloomKurt Cobain
3Come as You AreKurt Cobain
4BreedKurt Cobain
5LithiumKurt Cobain
6PollyKurt Cobain
7Territorial PissingsKurt Cobain
8Drain YouKurt Cobain
9Lounge ActKurt Cobain
10Stay AwayKurt Cobain
11On a PlainKurt Cobain
12Something in the WayKurt Cobain
13Endless, NamelessKurt Cobain, Krist Novoselic, Dave Grohl
As you can see tracks 1 and 13 now only appear once instead of three times.
I have searched high and low and cannot get my head around how to do it. I need to know how I can incorporate the concatenate part into this.
The sql for my function is here:
ALTER FUNCTION [dbo].[pfAlbum]
-- Add the parameters for the function here
(@AlbumTitle varchar(50))
RETURNS TABLE
AS
RETURN
(SELECT Tracks.TrackNumber AS TrackNo, Score.Title, Artists.Name AS Writers, Tracks.DiscNumber
FROM Score INNER JOIN
Recordings ON Score.ID = Recordings.ScoreID INNER JOIN
Tracks ON Recordings.ID = Tracks.RecordingID INNER JOIN
Album ON Tracks.AlbumID = Album.ID INNER JOIN
Score_Artist ON Score.ID = Score_Artist.ScoreID INNER JOIN
Roles ON Score_Artist.RoleID = Roles.ID INNER JOIN
Artists ON Score_Artist.ArtistID = Artists.ID
WHERE Album.Title = @AlbumTitle
)
If anyone can help it would be fantastic.
Kind Regards
Paul
August 17, 2012 at 9:40 am
Can you post ddl and sample along with desired output? Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
August 17, 2012 at 9:43 am
Here is a sample piece of code. Without the details of your tables and some sample data it is a bit difficult to modify it to work for you.
WITH CTE AS
(
SELECT
name as TableName,
object_id
FROM sys.tables WHERE schema_id = schema_id('dbo') and name like '%Lookup%'
)
SELECT TableName,
CommaList = STUFF((
SELECT ',' + c.name
FROM sys.columns c
WHERE c.object_id = CTE.object_id
ORDER BY c.column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY Tablename;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply