Help me write a SQL Crosstab query

Question

I have 3 tables that look like this:

tblVideo:
    VideoID     |   Video Name
          1             video 1
          2             video 2
          3             video 3
          4             video 4

tblCategory:
    CategoryID  |   CategoryName
          1           category1
          2           category2
          3           category3

tblVideoCategory:
     VideoID    |    CategoryID
          1               3
          2               1
          2               2
          3               1
          3               2
          3               3
          4               1

and I would like to write a query that would return a table that looks like this:

vVideoCategory:
VideoID   |   VideoName   |   category1   |   category2   |   category3
   1           video 1           false           false          true 
   2           video 2           true            true           false
   3           video 3           true            true           true
   4           video 4           true            false          false

I've tried looking around for examples but haven't quite found anything that seems the same. Any help would be appreciated, thanks.

Definitely looking for something that allows for changing and adding/deleting categories.

Answer 1

Use:

  SELECT v.videoid,
         v.video_name,
         COALESCE(MAX(CASE WHEN vc.categoryid = 1 THEN 'true' END), 'false') AS category1,
         COALESCE(MAX(CASE WHEN vc.categoryid = 2 THEN 'true' END), 'false') AS category2,
         COALESCE(MAX(CASE WHEN vc.categoryid = 3 THEN 'true' END), 'false') AS category3
    FROM tblvideo v
    JOIN tblvideocategory vc ON vc.videoid = v.videoid
GROUP BY v.videoid, v.video_name

SQL Server 2005+:

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @categoryid AS INT

DECLARE CUR CURSOR FAST_FORWARD FOR
   SELECT c.categoryid
     FROM tblcategory c
 ORDER BY c.categoryid

SET @SQL = N'SELECT v.videoid,
                    v.video_name, '

OPEN CUR
FETCH NEXT FROM CUR INTO @categoryid
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @SQL = @SQL + ' COALESCE(MAX(CASE WHEN vc.categoryid = '+ @categoryid +' THEN 'true' END), 'false') AS category'+ @categoryid +' ,'

  FETCH NEXT FROM CUR INTO @categoryid
END

CLOSE CUR;
DEALLOCATE CUR;

--Get rid of trailing comma at the end
SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)

SET @SQL = @SQL + ' FROM tblvideo v
                    JOIN tblvideocategory vc ON vc.videoid = v.videoid
                GROUP BY v.videoid, v.video_name 
                ORDER BY v.videoid, v.video_name '

BEGIN

  EXEC sp_executesql @SQL

END

Answer 2

just wanted to expand a little bit on what OMG Ponies provided me with:

@"            
            DECLARE @SQL AS NVARCHAR(4000)
            DECLARE @categoryid AS INT

            DECLARE CUR CURSOR FAST_FORWARD FOR
               SELECT c.categoryid
                 FROM Category c
             ORDER BY c.categoryid

            SET @SQL = 'SELECT v.videoid, v.title, v.Tags, '

            OPEN CUR
            FETCH NEXT FROM CUR INTO @categoryid
            WHILE @@FETCH_STATUS = 0
            BEGIN
              SET @SQL = @SQL + ' COALESCE(MAX(CASE WHEN vc.categoryid = ' + str(@categoryid) + ' THEN ''true'' END), ''false'') AS [category' + ltrim(str(@categoryid)) + '] ,'
              FETCH NEXT FROM CUR INTO @categoryid
            END

            CLOSE CUR;
            DEALLOCATE CUR;

            --Get rid of trailing comma at the end
            SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)

            SET @SQL = @SQL + ' FROM Video v
                                LEFT JOIN VideoCategory vc ON vc.videoid = v.videoid
                            " + where+ @"
                            GROUP BY v.videoid, v.title, v.Tags 
                            ORDER BY v.title, v.videoid, v.Tags '

            BEGIN
              EXEC sp_executesql @SQL
            END            
        "

the table names are a little different than I started out with, but the main changes are that I made the JOIN into a LEFT JOIN to include videos that had not yet been marked with a category. Also, I included a where clause.