这种行转列查询可以实现么?
zgbh rq bcbhbcmc
000001 2012-10-11001白班
000001 2012-10-12001白班
000001 2012-10-13001白班
000001 2012-10-14001白班
000001 2012-10-15001白班
000001 2012-10-16001白班
000001 2012-10-17001白班
000001 2012-10-18001白班
000001 2012-10-19001白班
000001 2012-10-20001白班
000001 2012-10-21001白班
000001 2012-10-22001白班
000001 2012-10-23001白班
000001 2012-10-24001白班
000001 2012-10-25001白班
000001 2012-10-26001白班
000001 2012-10-27001夜班
000001 2012-10-28001夜班
000001 2012-10-29001夜班
000001 2012-10-30001夜班
000002 2012-10-01001白班
....
000002 2012-10-30 001 夜班
转换成
zgbh 01 02 03 .... 27 28 29 30
000001 白班 白班 白班 夜班 夜班 夜班 夜班
000002 白班 null null null null null 夜班
[最优解释]
if(object_id('a')is not null)drop table a
go
create table a
(
zgbh varchar(50),
rq datetime,
bcbh varchar(30),
bcmc varchar(50)
)
go
insert into a
select '000001','2012-10-11','001','白班' union all
select '000001','2012-10-12','001','白班' union all
select '000001','2012-10-13','001','白班' union all
select '000001','2012-10-14','001','白班' union all
select '000001','2012-10-15','001','白班' union all
select '000001','2012-10-16','001','白班' union all
select '000001','2012-10-17','001','白班' union all
select '000001','2012-10-18','001','白班' union all
select '000001','2012-10-19','001','白班' union all
select '000001','2012-10-20','001','白班' union all
select '000001','2012-10-21','001','白班' union all
select '000001','2012-10-22','001','白班' union all
select '000001','2012-10-23','001','白班' union all
select '000001','2012-10-24','001','白班' union all
select '000001','2012-10-25','001','白班' union all
select '000001','2012-10-26','001','白班' union all
select '000001','2012-10-27','001','夜班' union all
select '000001','2012-10-28','001','夜班' union all
select '000001','2012-10-29','001','夜班' union all
select '000001','2012-10-30','001','夜班' union all
select '000002','2012-10-01','001','白班'
go
--动态SQL
declare @sql varchar(MAX)
select @sql = isnull(@sql+',',',') + 'MAX(case when rq = '+quotename(convert(varchar(24),[rq],121),'''')+' then '+quotename('bcmc')+'else null end) as '+quotename(convert(varchar(8),rq,11)) from a group by rq,bcmc
exec ('select zgbh '+@sql +'from a group by zgbh')
--静态SQL
select zgbh
,MAX(case when rq = '2012-10-11 00:00:00.000' then [bcmc]else null end )as [12/10/11]
,MAX(case when rq = '2012-10-12 00:00:00.000' then [bcmc]else null end )as [12/10/12]
,MAX(case when rq = '2012-10-13 00:00:00.000' then [bcmc]else null end )as [12/10/13]
,MAX(case when rq = '2012-10-14 00:00:00.000' then [bcmc]else null end )as [12/10/14]
,MAX(case when rq = '2012-10-15 00:00:00.000' then [bcmc]else null end )as [12/10/15]
,MAX(case when rq = '2012-10-16 00:00:00.000' then [bcmc]else null end )as [12/10/16]
,MAX(case when rq = '2012-10-17 00:00:00.000' then [bcmc]else null end )as [12/10/17]
,MAX(case when rq = '2012-10-18 00:00:00.000' then [bcmc]else null end )as [12/10/18]
,MAX(case when rq = '2012-10-19 00:00:00.000' then [bcmc]else null end )as [12/10/19]
,MAX(case when rq = '2012-10-20 00:00:00.000' then [bcmc]else null end )as [12/10/20]
,MAX(case when rq = '2012-10-21 00:00:00.000' then [bcmc]else null end )as [12/10/21]
,MAX(case when rq = '2012-10-22 00:00:00.000' then [bcmc]else null end )as [12/10/22]
,MAX(case when rq = '2012-10-23 00:00:00.000' then [bcmc]else null end )as [12/10/23]
,MAX(case when rq = '2012-10-24 00:00:00.000' then [bcmc]else null end )as [12/10/24]
,MAX(case when rq = '2012-10-25 00:00:00.000' then [bcmc]else null end )as [12/10/25]
,MAX(case when rq = '2012-10-26 00:00:00.000' then [bcmc]else null end )as [12/10/26]
,MAX(case when rq = '2012-10-27 00:00:00.000' then [bcmc]else null end )as [12/10/27]
,MAX(case when rq = '2012-10-28 00:00:00.000' then [bcmc]else null end )as [12/10/28]
,MAX(case when rq = '2012-10-29 00:00:00.000' then [bcmc]else null end )as [12/10/29]
,MAX(case when rq = '2012-10-30 00:00:00.000' then [bcmc]else null end )as [12/10/30]
,MAX(case when rq = '2012-10-01 00:00:00.000' then [bcmc]else null end )as [12/10/01]
from a group by zgbh
/*
zgbh 12/10/01 12/10/11 12/10/12 12/10/13 12/10/14 12/10/15 12/10/16 12/10/17 12/10/18 12/10/19 12/10/20 12/10/21 12/10/22 12/10/23 12/10/24 12/10/25 12/10/26 12/10/27 12/10/28 12/10/29 12/10/30
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
000001 NULL 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 白班 夜班 夜班 夜班 夜班
000002 白班 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/
select *
from (select zgbh,rq,bcmc from TB) as a
pivot(max(bcmc) for rq in ([2012-10-01],[2012-10-02],...[[2012-10-31]])) as b -- ...请补全 或者动态生成
