|
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',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,'bbb');
insert into tb_test(id,'ccc');
sql-------
select distinct id,[values]=stuff((select ','+[value] from tb_test t where id=tb_test.id
for xml path('')),1,'')
from tb_test
测试结果：
id????????? values
1?????????? aaa,bbb
2?????????? vvv
(2 row(s) affected)
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
