if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (ID int,北京 nvarchar(2),天津 nvarchar(2),广东 nvarchar(2))
insert into [TB]
select 1,'A','B','C' union all
select 2,'D','E','F'

select * from [TB]


select ID ,area,number
from (select ID,北京 ,天津,广东 
from TB)P
unpivot
(number for area in (北京 ,天津,广东 )) as unpvt;

/*
1    北京    A
1    天津    B
1    广东    C
2    北京    D
2    天津    E
2    广东    F*/