T-SQL Query needed for double-level string concatination from a single table

For Microsoft SQL Server 2005 T-SQL I'm having difficulty writing a query that concatenates two-levels such as Compact Disc discs and tracks (see table below).

My best attempt is using XML as a vehicle for string concatenation as described here.

Using a single query without a server-side cursor I'd like to generate a report of my music collection like below with one concatenated line output per database row per album.  Once I figure out the query I'll add the appropriate HTML as a detail.

OUTPUT ROW 1:

Album 1
  Disc 1
    1. She Loves You
    2. Yellow Submarine

  Disc 2
    1. Revolution
    2. Long and Winding Road
    3. Hey Jude


OUTPUT ROW 2:

Album 2
  Disc 1
    ...


OUTPUT ROW 3:

Album 3
  Disc 1
     ....

  Disc 2
    .....
create table Song (
  AlbumID int,
  DiscNbr tinyint,
  TrackNbr tinyint,
  Song varchar(50)
)

Open in new window

LVL 2
ZuZuPetalsAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Ah well... Good to hear you got your solution...

FWIW here is my last attempt (it is a challenge now) :)


select 'Album '+convert(varchar,a.albumid) +
    (select ','+'Disc '+convert(varchar,D.Discnbr) +
        (select ',' + convert(varchar,Tracknbr) + '.' + Song from song b where a.albumid = b.albumid and d.Discnbr=b.Discnbr for xml path(''))
    from song d where a.albumid=d.albumid group by d.discnbr for xml path(''))
from (select distinct albumid from song) a
0
 
expert_dharamCommented:
Did u tried this..

select AlbumID, STUFF(
      (SELECT  ',' + a.Song  AS [text()]
      from Song  a
      where a.AlbumID = b.AlbumID
      Order by a.Song
      for xml PATH('')),1,1,''      ) AS Comments_Concatenated
 from Song b
group by AlbumID
ORDER BY AlbumID
0
 
ZuZuPetalsAuthor Commented:
Yes, but that's one level.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
expert_dharamCommented:
Here you go..

SELECT AlbumID, DiscNbr,

STUFF(
      (SELECT  ', ' + cast(a.TrackNbr as varchar) + '.' + a.Song  AS [text()]
      FROM Song  a
      WHERE a.AlbumID = b.AlbumID and a.DiscNbr = b.DiscNbr
      Order by a.Song
      FOR XML PATH('')),1,1,''      ) AS Songs
      
      
 FROM Song b
group by AlbumID,DiscNbr
ORDER BY AlbumID,DiscNbr
0
 
ZuZuPetalsAuthor Commented:
Thanks expert_dharam... that's a partial solution: that creates one row per disc.    Now I'm trying to caress it to roll up all discs of an album to a single row.
0
 
Mark WillsTopic AdvisorCommented:
How about something like :


select ',' + 'Album '+convert(varchar,a.albumid) + ',' +
       'Disc '+convert(varchar,a.Discnbr) +
       (select ',' + convert(varchar,Tracknbr) + '.' + Song
        from song b where a.albumid = b.albumid and a.discnbr = b.discnbr for xml path('')) as [text()]
from (select distinct albumid, Discnbr from song) a  
for xml path('')
0
 
Mark WillsTopic AdvisorCommented:
Think I might be misunderstanding your output requirement... Treated it as one long concatenated string.

Thats not quite what you are after - is it...

0
 
Mark WillsTopic AdvisorCommented:
What about....


select listing
from (
      select albumid,0 as discnbr, 0 as tracknbr, 'Album '+convert(varchar,albumid) as listing from song group by albumid
      union
      select albumid,discnbr, 0 as tracknbr, '     Disc '+convert(varchar,Discnbr) as listing from song group by albumid, discnbr
      union
      select albumid,discnbr, tracknbr, '          '+convert(varchar,tracknbr)+'. '+song as listing from song
     )s
order by albumid,discnbr, tracknbr


0
 
ZuZuPetalsAuthor Commented:
@mark_wills: The first one concatenates to a single string and the second one does no concatenation.  Still looking for something in between (one row per album), but I appreciate the attempts.
0
 
Mark WillsTopic AdvisorCommented:
Right, knew I was misunderstanding :D

how about :

select 'Album '+convert(varchar,a.albumid) +
       (select ','+'Disc '+convert(varchar,Discnbr) +',' + convert(varchar,Tracknbr) + '.' + Song
        from song b where a.albumid = b.albumid for xml path('')) as [text()]
from (select distinct albumid from song) a  
0
 
ZuZuPetalsAuthor Commented:
@mark_willis: Wow, that's really close.  The only bad thing is that it repeats the disc number for every track.
0
 
ZuZuPetalsAuthor Commented:
My solution that I implemented is to use XML/text() to encapsulate the "tracks per disc" concatenation into a view for simplicity and readability.  I then use that view in a second XML/text() to concatenate the "discs per album" into a single string.  It takes 10 minutes to run on a million records but produces the exact result specified in the problem statement.  Thanks for everyone's help.
0
 
ZuZuPetalsAuthor Commented:
Awesome!!! That's it!  You did it Mark!  Thanks!

The only thing I added was order by DiscNbr,TrackNbr to get the ordering 1, 2, 3, etc. ascending.
0
 
ZuZuPetalsAuthor Commented:
YOU ROCK!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.