高手帮帮忙，用T-sql生成报表
用T-sql生成报表：
incert into person(name,birth) values ( '张先生 ', '1985-10-4 ')
…… ( '林先生 ', '1985-11-5 ')
……
10几条就行 ！
生成报表要求如下：
NO 01 02 03 04 05 06 07 08 09 10 11 12
1 张先生
2 林先生
3
4
5 ……
……
横着的是月份 谁是哪个月的生日 就把名字输出在相应月份下面
谢谢大家了
[解决办法]
create table person([name] nvarchar(100),birth datetime)
insert into person
select '张先生 ', '1985-10-4 ' union all
select '林先生 ', '1985-11-5 ' union all
select '张3 ', '1985-12-4 ' union all
select '张4 ', '1985-1-4 '
select m1 = (case month(birth) when 1 then [name] else ' ' end),
m2 = (case month(birth) when 2 then [name] else ' ' end),
m3 = (case month(birth) when 3 then [name] else ' ' end),
m4 = (case month(birth) when 4 then [name] else ' ' end),
m5 = (case month(birth) when 5 then [name] else ' ' end),
m6 = (case month(birth) when 6 then [name] else ' ' end),
m7 = (case month(birth) when 7 then [name] else ' ' end),
m8 = (case month(birth) when 8 then [name] else ' ' end),
m9 = (case month(birth) when 9 then [name] else ' ' end),
m10 = (case month(birth) when 10 then [name] else ' ' end),
m11 = (case month(birth) when 11 then [name] else ' ' end),
m12 = (case month(birth) when 12 then [name] else ' ' end)
from person group by [name],month(birth)
[解决办法]
---创建测试数据
declare @t table([name] varchar(10),birth datetime)
insert @t select '张先生 ', '1985-10-4 '
union all select '林先生 ', '1985-11-5 '
union all select '卢先生 ', '1985-05-7 '
union all select '刘先生 ', '1985-01-8 '
union all select '李先生 ', '1985-01-9 '
---查看测试数据
select * from @t
---查看结果
select case month(birth) when '01 ' then [name] else ' ' end as '01 ',
case month(birth) when '02 ' then [name] else ' ' end as '02 ',
case month(birth) when '03 ' then [name] else ' ' end as '03 ',
case month(birth) when '04 ' then [name] else ' ' end as '04 ',
case month(birth) when '05 ' then [name] else ' ' end as '05 ',
case month(birth) when '06 ' then [name] else ' ' end as '06 ',
case month(birth) when '07 ' then [name] else ' ' end as '07 ',
case month(birth) when '08 ' then [name] else ' ' end as '08 ',
case month(birth) when '09 ' then [name] else ' ' end as '09 ',
case month(birth) when '10 ' then [name] else ' ' end as '10 ',
case month(birth) when '11 ' then [name] else ' ' end as '11 ',
case month(birth) when '12 ' then [name] else ' ' end as '12 '
from @t
/*
01 02 03 04 05 06 07 08 09 10 11 12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
张先生
林先生
卢先生
刘先生
李先生
（所影响的行数为 5 行）
*/
[解决办法]
select m1 = (case month(birth) when 1 then [name] else ' ' end),
m2 = (case month(birth) when 2 then [name] else ' ' end),
m3 = (case month(birth) when 3 then [name] else ' ' end),
m4 = (case month(birth) when 4 then [name] else ' ' end),
m5 = (case month(birth) when 5 then [name] else ' ' end),
m6 = (case month(birth) when 6 then [name] else ' ' end),
m7 = (case month(birth) when 7 then [name] else ' ' end),
m8 = (case month(birth) when 8 then [name] else ' ' end),
m9 = (case month(birth) when 9 then [name] else ' ' end),
m10 = (case month(birth) when 10 then [name] else ' ' end),
m11 = (case month(birth) when 11 then [name] else ' ' end),
m12 = (case month(birth) when 12 then [name] else ' ' end)
from person group by [name],month(birth)
[解决办法]
--借用楼上数据
---创建测试数据
declare @t table([name] varchar(10),birth datetime)
insert @t select '张先生 ', '1985-10-4 '
union all select '林先生 ', '1985-11-5 '
union all select '卢先生 ', '1985-05-7 '
union all select '刘先生 ', '1985-01-8 '
union all select '李先生 ', '1985-01-9 '
---查看测试数据
--select * from @t
---查看结果
select
identity(int,1,1) as NO,
case when month(birth) = '01 ' then [name] else ' ' end as [01],
case when month(birth) = '02 ' then [name] else ' ' end as [02],
case when month(birth) = '03 ' then [name] else ' ' end as [03],
case when month(birth) = '04 ' then [name] else ' ' end as [04],
case when month(birth) = '05 ' then [name] else ' ' end as [05],
case when month(birth) = '06 ' then [name] else ' ' end as [06],
case when month(birth) = '07 ' then [name] else ' ' end as [07],
case when month(birth) = '08 ' then [name] else ' ' end as [08],
case when month(birth) = '09 ' then [name] else ' ' end as [09],
case when month(birth) = '10 ' then [name] else ' ' end as [10],
case when month(birth) = '11 ' then [name] else ' ' end as [11],
case when month(birth) = '12 ' then [name] else ' ' end as [12]
into #tp
from @t
--查询结果数据
select * from #tp
drop table #tp
[解决办法]
--借用楼上好多位的数据和代码
---创建测试数据
declare @t table([name] varchar(10),birth datetime)
insert @t select '张先生 ', '1985-10-4 '
union all select '林先生 ', '1985-11-5 '
union all select '卢先生 ', '1985-05-7 '
union all select '刘先生 ', '1985-01-8 '
union all select '李先生 ', '1985-01-9 '
union all select 'X先生 ', '1985-01-9 '
---查看测试数据
--select * from @t
---查看结果
select
no,
max(case when month(birth) = '01 ' then [name] else ' ' end) as [01],
max(case when month(birth) = '02 ' then [name] else ' ' end) as [02],
max(case when month(birth) = '03 ' then [name] else ' ' end) as [03],
max(case when month(birth) = '04 ' then [name] else ' ' end) as [04],
max(case when month(birth) = '05 ' then [name] else ' ' end) as [05],
max(case when month(birth) = '06 ' then [name] else ' ' end) as [06],
max(case when month(birth) = '07 ' then [name] else ' ' end) as [07],
max(case when month(birth) = '08 ' then [name] else ' ' end) as [08],
max(case when month(birth) = '09 ' then [name] else ' ' end) as [09],
max(case when month(birth) = '10 ' then [name] else ' ' end) as [10],
max(case when month(birth) = '11 ' then [name] else ' ' end) as [11],
max(case when month(birth) = '12 ' then [name] else ' ' end) as [12]
from (
select *,(select count(*) from @t where month(birth)=month(a.birth) and (birth <a.birth
or birth=a.birth and name <=a.name)) as No from @t a
) as t
group by no
--结果
no 01 02 03 04 05 06 07 08 09 10 11 12
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 刘先生 卢先生 张先生 林先生
2 X先生
3 李先生
（所影响的行数为 3 行）
[解决办法]
---LZ改下
if object_id( 'tempdb..#tmp ') is not null
drop table tempdb..#tmp
go
---创建测试数据
declare @t table([name] varchar(10),birth datetime)
insert @t select '张先生 ', '1985-10-4 '
union all select '林先生 ', '1985-11-5 '
union all select '卢先生 ', '1985-05-7 '
union all select '刘先生 ', '1985-01-8 '
union all select '李先生 ', '1985-01-9 '
---查看测试数据
select * from @t
---将结果放入临时表
select NO=identity(int,1,1),
case month(birth) when '01 ' then [name] else ' ' end as '01 ',
case month(birth) when '02 ' then [name] else ' ' end as '02 ',
case month(birth) when '03 ' then [name] else ' ' end as '03 ',
case month(birth) when '04 ' then [name] else ' ' end as '04 ',
case month(birth) when '05 ' then [name] else ' ' end as '05 ',
case month(birth) when '06 ' then [name] else ' ' end as '06 ',
case month(birth) when '07 ' then [name] else ' ' end as '07 ',
case month(birth) when '08 ' then [name] else ' ' end as '08 ',
case month(birth) when '09 ' then [name] else ' ' end as '09 ',
case month(birth) when '10 ' then [name] else ' ' end as '10 ',
case month(birth) when '11 ' then [name] else ' ' end as '11 ',
case month(birth) when '12 ' then [name] else ' ' end as '12 '
into #tmp from @t
---查看结果
select * from #tmp
/*
NO 01 02 03 04 05 06 07 08 09 10 11 12
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 张先生
2 林先生
3 卢先生
4 刘先生
5 李先生
（所影响的行数为 5 行）
*/
[解决办法]
create table person(id int identity,[name] varchar(10),birth datetime)
insert into person select '张先生 ', '1985-10-4 '
union all select '林先生 ', '1985-11-5 '
union all select '卢先生 ', '1985-05-7 '
union all select '刘先生 ', '1985-01-8 '
union all select '李先生 ', '1985-01-9 '
declare @sql varchar(1000)
set @sql= 'select id '
select @sql=@sql+ ',(case when datepart(mm,birth)= ' ' '+ltrim(birth)+ ' ' ' then Name else null end) as ' ' ' +ltrim(birth)+ '月 ' ' '
from (select distinct datepart(mm,birth) as birth from person) a
set @sql=@sql+ ' from person '
exec(@sql)
id 1月 5月 10月 11月
----------- ---------- ---------- ---------- ----------
1 NULL NULL 张先生 NULL
2 NULL NULL NULL 林先生
3 NULL 卢先生 NULL NULL
4 刘先生 NULL NULL NULL
5 李先生 NULL NULL NULL
(5 行受影响)
