34,838
社区成员




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
*/
参见:
/*如何有SOL语句查询
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