Avatar of ZuZuPetals
ZuZuPetals
Flag for United States of America

asked on 

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

Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
ZuZuPetals

8/22/2022 - Mon