Including null values in unpivoted column

Question

Consider the following table:

id  title   genre1  genre2  genre3  genre4  genre5
1   Movie1  Thriller    Crime   NULL    NULL    NULL
2   Movie2  Fantasy NULL    NULL    NULL    NULL
3   Movie3  Political   Philosophical   Historical  NULL    NULL
4   Movie4  Science Fiction NULL    NULL    NULL    NULL

When I unpivot genre1, genre2, ... the NULL values are removed, so that, for example, the rows for Movie1 would be:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2

Is there a way to include NULL values, for example:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2
1  Movie1 NULL      genre3
1  Movie1 NULL      genre4
1  Movie1 NULL      genre5

I can SELECT ISNULL(genre1, '*'), ISNULL(genre2, '*') ..., but suppose I want the values to appear as NULL? Is there a better way than:

WITH 
-- SELECT that unpivots table
AS unpivoted
SELECT unpivoted.id, 
  unpivoted.title, 
  ,CASE unpivoted.genre WHEN '*' THEN NULL ELSE unpivoted.genre END 
  ,unpivoted.original_transaction
FROM unpivoted

Also, I notice that the query that returns the source data to be unpivoted MUST have an alias. For example:

SELECT ... FROM
  -- *must* give it an alias or it's a syntax error
  (SELECT ... FROM) AS source 
  UNPIVOT(genre for original_column IN (genre1,genre2, genre3,genre4,genre5))

Does anyone know why? I have not found a way to refer to the alias anywhere in the query.

Answer 1

You can do the unpivot in a cross apply instead to get the result you want.

declare @T table
(
  id int,
  title varchar(50),
  genre1 varchar(50),
  genre2 varchar(50),
  genre3 varchar(50),
  genre4 varchar(50),
  genre5 varchar(50)
);

insert into @T values
(1, 'Movie1', 'Thriller', 'Crime',  NULL,  NULL, NULL),
(2, 'Movie2', 'Fantasy', NULL, NULL, NULL, NULL),
(3, 'Movie3', 'Political', 'Philosophical', 'Historical', NULL, NULL),
(4, 'Movie4', 'Science Fiction', NULL, NULL, NULL, NULL);

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (
              select T.genre1, 'genre1' union all
              select T.genre2, 'genre2' union all
              select T.genre3, 'genre3' union all
              select T.genre4, 'genre4' union all
              select T.genre5, 'genre5'
              ) as G(genre, original_column);

If you are on SQL Server 2008 or later you can use the Table Value Constructor instead of union all as suggested by Vladimir Baranov in a comment.

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (values (T.genre1, 'genre1'),
                      (T.genre2, 'genre2'),
                      (T.genre3, 'genre3'),
                      (T.genre4, 'genre4'),
                      (T.genre5, 'genre5')
              ) as G(genre, original_column);