--竖表变行表
来源：互联网 发布：3d 投影 2d 矩阵 编辑：程序博客网 时间：2024/05/16 15:39
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
--竖表变行表
--创建表
createtableA(
工号varchar(20),
日期smalldatetime,
时间varchar(20)
)
--插入测试数据
insertintoAselect'01','2004-10-01','07:50'
unionselect'01','2004-10-01','11:35'
unionselect'01','2004-10-01','14:20'
unionselect'01','2004-10-02','08:01'
unionselect'01','2004-10-02','14:30'
unionselect'02','2004-10-01','07:55'
unionselect'02','2004-10-02','07:58'
unionselect'03','2004-10-01','07:56'
--创建存储过程
createprocp_t
as
declare@idvarchar(20),@datesmalldatetime,@timevarchar(50)
select*into#afromAorderby工号,日期,时间
update#aset时间=casewhen(工号=@id)and(日期=@date)then@timeelse时间end,
@time=casewhen(工号=@id)and(日期=@date)then@time+','+时间else时间end,
@id=工号,@date=日期
select工号,日期,max(时间)as时间from#agroupby工号,日期orderby工号
--执行
execp_t
--测试结果
工号日期时间
---------------------------------------------
012004-10-0100:00:0007:50,11:35,14:20
012004-10-0200:00:0008:01,14:30
022004-10-0100:00:0007:55
022004-10-0200:00:0007:58
032004-10-0100:00:0007:56
（所影响的行数为5行）
select[Empid]from[Employee]
得出结果：
张三
李四
王五
...
...
想要将它合并成这种结果：
张三,李四,王五...,...
declare@svarchar(8000)
set@s=''
select@s=@s+','+[Empid]from[Employee]
printstuff(@s,1,1,'')
declare@sqlvarchar(2000)
set@sql=''
select@sql=@sql+[Empid]+','from[Employee]
set@sql=left(@sql,len(@sql)-1)
print@sql
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
--竖表变行表
--创建表
createtableA(
工号varchar(20),
日期smalldatetime,
时间varchar(20)
)
--插入测试数据
insertintoAselect'01','2004-10-01','07:50'
unionselect'01','2004-10-01','11:35'
unionselect'01','2004-10-01','14:20'
unionselect'01','2004-10-02','08:01'
unionselect'01','2004-10-02','14:30'
unionselect'02','2004-10-01','07:55'
unionselect'02','2004-10-02','07:58'
unionselect'03','2004-10-01','07:56'
--创建存储过程
createprocp_t
as
declare@idvarchar(20),@datesmalldatetime,@timevarchar(50)
select*into#afromAorderby工号,日期,时间
update#aset时间=casewhen(工号=@id)and(日期=@date)then@timeelse时间end,
@time=casewhen(工号=@id)and(日期=@date)then@time+','+时间else时间end,
@id=工号,@date=日期
select工号,日期,max(时间)as时间from#agroupby工号,日期orderby工号
--执行
execp_t
--测试结果
工号日期时间
---------------------------------------------
012004-10-0100:00:0007:50,11:35,14:20
012004-10-0200:00:0008:01,14:30
022004-10-0100:00:0007:55
022004-10-0200:00:0007:58
032004-10-0100:00:0007:56
（所影响的行数为5行）
select[Empid]from[Employee]
得出结果：
张三
李四
王五
...
...
想要将它合并成这种结果：
张三,李四,王五...,...
declare@svarchar(8000)
set@s=''
select@s=@s+','+[Empid]from[Employee]
printstuff(@s,1,1,'')
declare@sqlvarchar(2000)
set@sql=''
select@sql=@sql+[Empid]+','from[Employee]
set@sql=left(@sql,len(@sql)-1)
print@sql
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
