sqlserver2005一些不常用的sql语法

1.行列转换

--测试环境
Create table T(日期 datetime,时间 varchar(20),售货金额 int)
insert into T select '2006-01-02','早上',50
union all select '2006-01-02','中午',20
union all select '2006-01-02','晚上',30
union all select '2006-01-02','零晨',40
union all select '2006-01-03','早上',40
union all select '2006-01-03','中午',60
union all select '2006-01-03','晚上',50
union all select '2006-01-03','零晨',50
union all select '2006-01-04','早上',80
union all select '2006-01-04','中午',60
union all select '2006-01-04','晚上',20
union all select '2006-01-04','零晨',40
--查询
select * ,金额小计=(select sum(售货金额) from T where 日期=PT.日期 ) from T as TAB
PIVOT
( max(售货金额)
  for 时间 in ([早上],[中午],[晚上],[零晨])
) as PT
--结果
/*
日期                      早上          中午          晚上          零晨          金额小计
----------------------- ----------- ----------- ----------- ----------- -----------
2006-01-02 00:00:00.000 50          20          30          40          140
2006-01-03 00:00:00.000 40          60          50          50          200
2006-01-04 00:00:00.000 80          60          20          40          200

(3 行受影响)
*/

 

2.xml处理

测试环境

Create table tb_test(id int,value varchar(20))

insert into tb_test(id,value) values (1,'aaa');

insert into tb_test(id,value) values (1,'bbb');

insert into tb_test(id,value) values (1,'ccc');

sql-------

select distinct id, [values]=stuff((select ','+[value] from tb_test t where id=tb_test.id
for xml path('')), 1, 1, '')
from tb_test

测试结果：

id          values
1           aaa,bbb
2           vvv

(2 row(s) affected)