Simple example of using CROSS JOIN with UNION ALL instead of UNPIVOT to unpivot data so as to include nulls in the results. This is especially useful when the data custodian wants to differentiate between data that was N/A and a zero value.

unpivot_using_cross_join_and_union_all.sql

-- 1. Create a table of pivoted data (the kind of thing you might expect to be imported from CSV)

/****** Object:  Table [dbo].[FruitBaskets]    Script Date: 3/10/2014 10:48:23 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FruitBaskets](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](64) NOT NULL,
	[Apples] [int] NULL,
	[Oranges] [int] NULL,
	[Bananas] [int] NULL,
 CONSTRAINT [PK_FruitBaskets] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- 2. Insert some pivoted data

insert into FruitBaskets (Name, Apples, Oranges, Bananas) values ('Basket A', 1, 1, 1)
insert into FruitBaskets (Name, Apples, Oranges, Bananas) values ('Basket B', 2, 0, 1)
insert into FruitBaskets (Name, Apples, Oranges, Bananas) values ('Basket C', 1, 2, NULL)

select * from FruitBaskets

-- 3. Unpivot the data using cross join and union all to include null values in results

select Id,
	Name,
	unpivoted.Fruit as 'Fruit',
	Quantity = case unpivoted.Fruit
		when 'Apples' then Apples
		when 'Oranges' then Oranges
		when 'Bananas' then Bananas
	end
from (
	-- select from our pivoted data
	select Id, Name, Apples, Oranges, Bananas
	from FruitBaskets
) pivoted
cross join
(
	select 'Apples' union all
	select 'Oranges' union all
	select 'Bananas'
) unpivoted (Fruit)