无列名 field,Month
2009-01-01 1 2009-01-01
2009-01-01 2009-01-01
2009-01-01 2009-01-01
2009-01-02 2009-01-02
2009-01-02 2 2009-01-02
2009-01-02 2009-01-02
2009-01-03 2009-01-03
2009-01-03 2009-01-03
2009-01-03 3 2009-01-03
--你的month都等于第一列,为什么field不全是1
2009-01-01 1 2009-01-01
2009-01-01 2009-01-01
2009-01-01 2009-01-01
2009-01-02 2009-01-02
2009-01-02 2 2009-01-02
2009-01-02 2009-01-02
2009-01-03 2009-01-03
2009-01-03 2009-01-03
2009-01-03 3 2009-01-03
--你的month都等于第一列,为什么field不全是1
b.[Month] as 无列名
,c.field
,b.[Month]
from (
select distinct field from #t
) as a cross join (
select distict [Month] from #t
where [Month] between @dateFrom and @dateTo
) as b left join #t c
on a.field = c.field and b.[Month] = c.[Month]
b.[Month] as 无列名
,c.field
,b.[Month]
from (
select distinct field from #t
) as a cross join (
select distict [Month] from #t
where [Month] between @dateFrom and @dateTo
) as b left join #t c
on a.field = c.field and b.[Month] = c.[Month]
order by b.[Month],a.field
go
create table tb (Name varchar(20),kecheng varchar(20),chengji int)
insert into tb values('王学','语文',80)
insert into tb values('王学','数学',73)
insert into tb values('王学','英语',79)
insert into tb values('陈费','语文',80)
insert into tb values('陈费','数学',73)
insert into tb values('陈费','英语',80)
怎样让结果 变成这样
王学 陈费
语文 80 80
数学 73 73
英语 79 80 我看过那行列转换了,但还是不会,麻烦给我写写,谢谢
2009-01-01 1 2009-01-01
2009-01-01 1 2009-01-01
2009-01-01 1 2009-01-01
2009-01-02 2 2009-01-02
2009-01-02 2 2009-01-02
2009-01-02 2 2009-01-02
2009-01-03 3 2009-01-03
2009-01-03 3 2009-01-03
2009-01-03 3 2009-01-03
create table #t (
field int,
[Month] datetime
)
insert #t select
-------------------
1, '2009-01-01'
union all select
2, '2009-01-02'
union all select
3, '2009-01-03' declare @dateFrom datetime
declare @dateTo datetime
set @dateFrom='2009-01-01'
set @dateTo ='2009-01-03' select
b.[Month] as 无列名
,c.field
,b.[Month]
from (
select distinct field from #t
) as a cross join (
select distinct [Month] from #t
where [Month] between @dateFrom and @dateTo
) as b left join #t c
on a.field = c.field and b.[Month] = c.[Month]
order by b.[Month],a.field--结果
无列名 field Month
----------------------- ----------- -----------------------
2009-01-01 00:00:00.000 1 2009-01-01 00:00:00.000
2009-01-01 00:00:00.000 NULL 2009-01-01 00:00:00.000
2009-01-01 00:00:00.000 NULL 2009-01-01 00:00:00.000
2009-01-02 00:00:00.000 NULL 2009-01-02 00:00:00.000
2009-01-02 00:00:00.000 2 2009-01-02 00:00:00.000
2009-01-02 00:00:00.000 NULL 2009-01-02 00:00:00.000
2009-01-03 00:00:00.000 NULL 2009-01-03 00:00:00.000
2009-01-03 00:00:00.000 NULL 2009-01-03 00:00:00.000
2009-01-03 00:00:00.000 3 2009-01-03 00:00:00.000(9 行受影响)