5

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.

1
  • Thanks for the answer. Can anyone answer part (2), which was: Does anyone know why the query that returns the source data to be unpivoted MUST have an alias, even though the alias is apparently useless? – Test User Oct 14 '15 at 12:03
7

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);
2
  • 5
    Instead of UNION ALL you can also use VALUES syntax: cross apply ( values (T.genre1,'genre1'), (T.genre2,'genre2'), (T.genre3,'genre3'), (T.genre4,'genre4'), (T.genre5,'genre5') ) AS G(genre, original_column) – Vladimir Baranov Oct 14 '15 at 10:18
  • 1
    @Vladimir Baranov: Less typing is always good. i don't know how to upvote your comment. – Test User Oct 14 '15 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.