- 爱易网页
-
MSSQL教程
- 高手帮帮忙,用T-sql生成报表,该如何解决
日期:2014-05-19 浏览次数:20630 次
高手帮帮忙,用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