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).
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))