22,207
社区成员
发帖
与我相关
我的任务
分享拜托了.行转列求和
#T(TypeID NVARCHAR(10),InputDate SAMLLDATETIME,ItemNumber NVARCHAR(10),QTY INT)
SELECT 'A','2009-1-5','NO1',20
SELECT 'A','2009-1-5','NO2',30
SELECT 'B','2009-1-5','NO3',12
SELECT 'A','2009-2-5','NO1',20
SELECT 'A','2009-4-5','NO1',20
SELECT 'A','2009-4-5','NO2',20
SELECT 'B','2009-4-5','NO3',21
SELECT 'B','2009-4-5','NO3',22
如果别人找2009年1月 到4月
达到的效果如:
TypeID,ItemNumber,2009-1,2009-2,2009-3,2009-4
'A' NO1 20 20 0 20
'A' NO2 30 0 0 20
A-Total, 50 20 40
'B' NO3 12 0 0 22
B-Total 12 22
星期五根据libin和梁哥的代码已经可以动态行转列了.但是现在要加个求和.
如果要找动态的字段求和.还真不知道怎么做哦.....
不知道可以在数据库里面实现吗?真是伤脑筋,都快放弃了.
SELECT 'A','2009-1-5','NO1',20
SELECT 'A','2009-1-5','NO2',30
SELECT 'B','2009-1-5','NO3',12
SELECT 'A','2009-2-5','NO1',20
SELECT 'A','2009-4-5','NO1',20
SELECT 'A','2009-4-5','NO2',20
SELECT 'B','2009-4-5','NO3',21
SELECT 'B','2009-4-5','NO3',22
如果别人找2009年1月 到4月
达到的效果如:
TypeID,ItemNumber,2009-1,2009-2,2009-3,2009-4
'A' NO1 20 20 0 20
'A' NO2 30 0 0 20
A-Total, 50 20 40
'B' NO3 12 0 0 22
B-Total 12 22
星期五根据libin和梁哥的代码已经可以动态行转列了.但是现在要加个求和.
如果要找动态的字段求和.还真不知道怎么做哦.....
不知道可以在数据库里面实现吗?真是伤脑筋,都快放弃了.
...全文
请发表友善的回复…
发表回复
--小F-- 2009-06-29
- 打赏
- 举报
CREATE TABLE #T(TypeID NVARCHAR(10),InputDate SMALLDATETIME,ItemNumber NVARCHAR(10),QTY INT)
INSERT #T
SELECT 'A','2009-1-5','NO1',20 UNION ALL
SELECT 'A','2009-1-5','NO2',30 UNION ALL
SELECT 'B','2009-1-5','NO3',12 UNION ALL
SELECT 'A','2009-2-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO2',20 UNION ALL
SELECT 'B','2009-4-5','NO3',21 UNION ALL
SELECT 'B','2009-4-5','NO3',22
declare @cmd varchar(8000)
select @cmd=isnull(@cmd,'')+',['+t.[Date]+']=sum(case when datediff(mm,'''+t.[Date]+'-01'',InputDate)=0 then QTY else 0 end)'
from
(select distinct convert(char(7),InputDate,120) as [Date] from #T
where
convert(char(7),InputDate,120) between '2009-01' and '2009-04') t
set @cmd=
'select
case when GROUPING(ItemNumber)=1 then TypeID + ''Total'' else TypeID end AS TypeID
,ItemNumber'+@cmd+' from #t group by TypeID,ItemNumber
with rollup
having
grouping(TypeID)=0'
exec(@cmd)
drop table #t
/*
（所影响的行数为 8 行）
TypeID ItemNumber 2009-01 2009-02 2009-04
--------------- ---------- ----------- ----------- -----------
A NO1 20 20 20
A NO2 30 0 20
ATotal NULL 50 20 40
B NO3 12 0 43
BTotal NULL 12 0 43*/
mfkigeypm 2009-06-29
- 打赏
- 举报
哎，真正见到高手了
ai_li7758521 2009-06-29
- 打赏
- 举报
CREATE TABLE #T(TypeID NVARCHAR(10),InputDate SMALLDATETIME,ItemNumber NVARCHAR(10),QTY INT)
INSERT #T
SELECT 'A','2009-1-5','NO1',20 UNION ALL
SELECT 'A','2009-1-5','NO2',30 UNION ALL
SELECT 'B','2009-1-5','NO3',12 UNION ALL
SELECT 'A','2009-2-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO2',20 UNION ALL
SELECT 'B','2009-4-5','NO3',21 UNION ALL
SELECT 'B','2009-4-5','NO3',22
declare @cmd varchar(8000)
select @cmd=isnull(@cmd,'')+',['+t.Date+']=sum(case when datediff(mm,'''+t.Date+'-01'',InputDate)=0 then QTY else 0 end)'
from
(select distinct convert(char(7),InputDate,120) as Date from #T
where convert(char(7),InputDate,120) between '2009-01' and '2009-04') t
set @cmd='select case when GROUPING(ItemNumber)=1 then TypeID + ''Total'' else TypeID end AS TypeID
,ItemNumber'+@cmd+' from #t group by TypeID,ItemNumber with rollup having grouping(TypeID)=0'
exec(@cmd)
TypeID ItemNumber 2009-01 2009-02 2009-04
--------------- ---------- ----------- ----------- -----------
A NO1 20 20 20
A NO2 30 0 20
ATotal NULL 50 20 40
B NO3 12 0 43
BTotal NULL 12 0 43
(5 行受影响)
lgx0914 2009-06-29
- 打赏
- 举报
这就是为什么他们是牛人的原因
[Quote=引用 6 楼 feiyang431 的回复:]
为什么你们写代码的速度那么快呀。。
从发帖到回帖才四分钟就OK了。。
[/Quote]
[Quote=引用 6 楼 feiyang431 的回复:]
为什么你们写代码的速度那么快呀。。
从发帖到回帖才四分钟就OK了。。
[/Quote]
fzcheng 2009-06-29
- 打赏
- 举报
这是2005新增函数吗?好陌生哦.晕死...
feiyang431 2009-06-29
- 打赏
- 举报
为什么你们写代码的速度那么快呀。。
从发帖到回帖才四分钟就OK了。。
从发帖到回帖才四分钟就OK了。。
--小F-- 2009-06-29
- 打赏
- 举报
其实就是用到grouping
with rollup group by 就OK了
with rollup group by 就OK了
fzcheng 2009-06-29
- 打赏
- 举报
谢谢。有了结果。但是没有看懂，现研究下咯。5555
永生天地 2009-06-29
- 打赏
- 举报
这是什么牛，速度
--小F-- 2009-06-29
- 打赏
- 举报
--参考
你用他们的代码修改下就OK了
首先创建测试表、添加数据。
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)
insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即
可。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end
a,
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup
情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end
a,
b,
sum(c),sum(d),sum(e) from #t
group by a,b with rollup
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1
then '合计' else cast(a as varchar) end a,
b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(c)=0 or grouping(a)=1
情况三：有多个分类汇总列，需要全部的小计和合计。
select case when grouping(a)=1 then '合计' else cast(a as varchar) end
a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b
as varchar) end b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c
as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
另外一种显示小计的方式
select case when grouping(a)=1 then '合计'
when grouping(b)=1 then cast(a as varchar)+'小计'
else cast(a as varchar) end a,
case when grouping(b)=0 and grouping(c)=1
then cast(b as varchar)+'小计' else cast(b as varchar) end b,
case when grouping(c)=1 and grouping(b)=0
then '' else cast(c as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
情况四：有多个分类汇总列，需要部分的小计和合计
select case when grouping(a)=1 then '合计' else cast(a as varchar) end
a,
b,
case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c
as varchar) end c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=0
select case when grouping(a)=1 then '合计' else cast(a as varchar) end
a,
case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b
as varchar) end b,
c,
sum(d),sum(e) from #t
group by a,b,c with rollup
having grouping(a)=1 or grouping(b)=1 or grouping(c)=
liangCK 2009-06-29
- 打赏
- 举报
CREATE TABLE #T(TypeID NVARCHAR(10),InputDate SMALLDATETIME,ItemNumber NVARCHAR(10),QTY INT)
INSERT #T
SELECT 'A','2009-1-5','NO1',20 UNION ALL
SELECT 'A','2009-1-5','NO2',30 UNION ALL
SELECT 'B','2009-1-5','NO3',12 UNION ALL
SELECT 'A','2009-2-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO1',20 UNION ALL
SELECT 'A','2009-4-5','NO2',20 UNION ALL
SELECT 'B','2009-4-5','NO3',21 UNION ALL
SELECT 'B','2009-4-5','NO3',22
DECLARE @year INT,@start_month INT,@end_month INT;
SELECT @year = 2009,@start_month = 1,@end_month = 4;
DECLARE @columns VARCHAR(8000);
SET @columns = '';
DECLARE @i INT;
SET @i = @start_month;
WHILE @i <= @end_month
SELECT
@columns = @columns + ',SUM(CASE WHEN CONVERT(VARCHAR(7),InputDate,120) = '''
+ RTRIM(@year) + '-' + RIGHT(100 + @i,2)
+ ''' THEN QTY ELSE 0 END) AS ['
+ RTRIM(@year) + '-' + RIGHT(100 + @i,2) + ']',
@i = @i + 1;
DECLARE @cmd NVARCHAR(4000)
SET @cmd =
'SELECT CASE WHEN GROUPING(ItemNumber)=1 THEN
TypeID + ''Total''
ELSE TypeID END AS TypeID,ItemNumber' + @columns
+ ' FROM #T WHERE YEAR(InputDate) = @year'
+ ' AND MONTH(InputDate) BETWEEN @start_month AND @end_month '
+ ' GROUP BY TypeID,ItemNumber WITH ROLLUP HAVING GROUPING(TypeID)=0';
EXEC sp_executesql @cmd,N'@year INT,@start_month INT,@end_month INT',
@year,@start_month,@end_month;
DROP TABLE #T;
/*
TypeID ItemNumber 2009-01 2009-02 2009-03 2009-04
--------------- ---------- ----------- ----------- ----------- -----------
A NO1 20 20 0 20
A NO2 30 0 0 20
ATotal NULL 50 20 0 40
B NO3 12 0 0 43
BTotal NULL 12 0 0 43
(5 行受影响)
*/
