－－行转列

create table ta(col1  varchar(30),col2 varchar(10))

insert ta

select '车辆[68]状态[抢救转送]' as '状态','中区' as '区域' union all 

select '车辆[71]状态[分配任务]' as '状态','中区' as '区域' union all 

select '车辆[66]状态[抢救转送]' as '状态','中区' as '区域' union all 

select '车辆[0840]状态[抢救转送]' as '状态','通州' as '区域' union all 

select '车辆[8781]状态[驶向现场]' as '状态','朝阳' as '区域' union all 

select '车辆[168]状态[抢救转送]' as '状态','北区' as '区域' union all 

select '车辆[0839]状态[驶向现场]' as '状态','房山' as '区域' union all 

select '车辆[173]状态[抢救转送]' as '状态','西区' as '区域' union all 

select '车辆[0366]状态[收到指令]' as '状态','大兴' as '区域' union all 

select '车辆[0960]状态[收到指令]' as '状态','通州' as '区域' union all 

select '车辆[0930]状态[抢救转送]' as '状态','通州' as '区域' union all 

select '车辆[79]状态[分配任务]' as '状态','南区' as '区域' union all 

select '车辆[007]状态[分配任务]' as '状态','中区' as '区域' 

go

declare @s varchar(8000)



select @s = isnull(@s+',','') + '['+col2+']=max(case when col2 = '''+col2+''' then col1 else null end)'

from (select distinct col2 from ta ) a

--print @s



exec('select right(col1,6) as 状态 ,'+@s+' from ta  group by right(col1,6)')



drop table ta



/*

状态           北区                             朝阳                             大兴                             房山                             南区                             通州                             西区                             中区                             

------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 

[分配任务]       NULL                           NULL                           NULL                           NULL                           车辆[79]状态[分配任务]                 NULL                           NULL                           车辆[71]状态[分配任务]

[抢救转送]       车辆[168]状态[抢救转送]                NULL                           NULL                           NULL                           NULL                           车辆[0930]状态[抢救转送]               车辆[173]状态[抢救转送]                车辆[68]状态[抢救转送]

[驶向现场]       NULL                           车辆[8781]状态[驶向现场]               NULL                           车辆[0839]状态[驶向现场]               NULL                           NULL                           NULL                           NULL

[收到指令]       NULL                           NULL                           车辆[0366]状态[收到指令]               NULL                           NULL                           车辆[0960]状态[收到指令]               NULL                           NULL



*/

参见：

/*如何有ＳＯＬ语句查询 

a b c 

7 2 3 

转换成 

name values 

a     7 

b     2 

c     3*/



use tempdb

go

if object_id('tempdb..#') is not null

drop table #



create table #(a int

                ,b int

                ,c int)

insert into #

select 7,2,3

select * from #



select name,[values]

from (select a,b,c from # )  as a

unpivot

([values] for [name]

        in (a,b,c)

) as unpvt