这种行转列查询可以实现么?

Question

zgbh          rq        bcbh bcmc
000001     2012-10-11 001 白班                          
000001     2012-10-12 001 白班                          
000001     2012-10-13 001 白班                          
000001     2012-10-14 001 白班                          
000001     2012-10-15 001 白班                          
000001     2012-10-16 001 白班                          
000001     2012-10-17 001 白班                          
000001     2012-10-18 001 白班                          
000001     2012-10-19 001 白班                          
000001     2012-10-20 001 白班                          
000001     2012-10-21 001 白班                          
000001     2012-10-22 001 白班                          
000001     2012-10-23 001 白班                          
000001     2012-10-24 001 白班                          
000001     2012-10-25 001 白班                          
000001     2012-10-26 001 白班                          
000001     2012-10-27 001 夜班                          
000001     2012-10-28 001 夜班                          
000001     2012-10-29 001 夜班                          
000001     2012-10-30 001 夜班      
000002          2012-10-01 001 白班 
....
000002          2012-10-30      001     夜班
转换成
zgbh     01     02     03 ....   27    28   29   30
000001   白班   白班   白班      夜班 夜班 夜班 夜班
000002   白班   null   null      null null null  夜班

Answer 1

可以实现
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 -- ...请补全 或者动态生成

Answer 2

当然可以实现，通过case或者分表

Answer 3

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,bcmcexec ('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 行受影响)
*/