CODEWITHSUNDEEP
sqlsql-servert-sql
Nathan Rice
Nathan Rice

Reputation: 3111

Conditional TSQL Grouping

I am trying to craft a TSQL statement that will group album and and artists and sort by artist name. Each track has all of the ID3 data with album and artist names. When certain albums have more than one artist, the sort order gets messed up.

How do I conditionally group artists as "Various Artists" when the artist names don't match but the album names do?

Here is my simplified TSQL statement:

SELECT songid, artist, album, tracknumber 
FROM mp3
ORDER BY artist, album, tracknumber

If it matters this is Microsoft SQL Server that I'm working in.

Thank you in advance for your time.

Upvotes: 1

Views: 169

Answers (1)

Jamie F
Jamie F

Reputation: 23789

I haven't tested this, but the general idea should be apparent nonetheless...

The same expression that is used for the AlbumArtist field can be used for grouping.

WITH
   VariousArtistsCTE
   AS
   (SELECT
      album,
      CASE WHEN 
         COUNT(DISTINCT Artist) = 1
      THEN
         0
      ELSE
         1
      END AS VariousArtists
   FROM mp3
   GROUP BY album)

SELECT
   songid,
   artist AS TrackArtist,
   album,
   tracknumber
   CASE WHEN va.VariousArtists = 1 THEN 'Various Artists'
   ELSE artist END As AlbumArtist 
FROM
   mp3
LEFT OUTER JOIN
   VariousArtistsCTE va
   ON mp3.Album = va.Album

Upvotes: 3

Related Questions