表
工单号 时间 处理的城市需要以如下格式统计数据
长沙 湘潭 株洲 衡阳 郴州 常德 益阳 娄底 邵阳 岳阳 自治州 张家界 怀化 永州 全省
1月
2月
3月
4月
5月
6月
7月
8月
9月
10月
11月
12月
怎样做才好?
工单号 时间 处理的城市需要以如下格式统计数据
长沙 湘潭 株洲 衡阳 郴州 常德 益阳 娄底 邵阳 岳阳 自治州 张家界 怀化 永州 全省
1月
2月
3月
4月
5月
6月
7月
8月
9月
10月
11月
12月
怎样做才好?
sum(case 处理的城市 when '湘潭' then 数量 else 0 end) as '湘潭',
sum(case 处理的城市 when '株洲' then 数量 else 0 end) as '株洲'
from (
select count(1) as 数量,month(时间) as 月份,处理的城市
from tableName
group by month(时间),处理的城市 ) t
group by 月份就写了长株潭的,其他的你自己按格式补就可以了
INSERT INTO #Test
SELECT '001','2009-1-1','长沙' union all
SELECT '002','2009-1-1','长沙' union all
SELECT '003','2009-3-1','长沙' union all
SELECT '004','2009-2-1','株洲' union all
SELECT '005','2009-3-1','湘潭' union all
SELECT '006','2009-1-1','湘潭' union all
SELECT '007','2009-1-4','湘潭'select 月份,sum(case 处理的城市 when '长沙' then 数量 else 0 end) as '长沙',
sum(case 处理的城市 when '湘潭' then 数量 else 0 end) as '湘潭',
sum(case 处理的城市 when '株洲' then 数量 else 0 end) as '株洲'
from (
select count(1) as 数量,month(时间) as 月份,处理的城市
from #Test
group by month(时间),处理的城市 ) t
group by 月份DROP TABLE #Test
期待数据是表中记录数
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html?83718
declare @t1 table( num int , dtime Datetime , city varchar(30))
insert into @t1 values( 1 , '2009-9-6','长沙')
insert into @t1 values( 1 , '2009-7-6','湘潭') select monthname,isnull([长沙],0)as '长沙',
isnull([湘潭],0)as '湘潭',
isnull([株洲],0)as '株洲',
isnull([衡阳],0)as '衡阳',
isnull([郴州],0)as '郴州',
isnull([常德],0)as '常德',
isnull([益阳],0)as '益阳',
isnull([娄底],0)as '娄底',
isnull([邵阳],0)as '邵阳',
isnull([自治州],0)as '自治州',
isnull([张家界],0)as '张家界',
isnull([怀化],0)as '怀化',
isnull([永州],0)as '永州',
isnull([全省],0)as '全省'
from (
select '1月' as monthname ,'1'as months
union all
select '2月' as monthname ,'2'as months
union all
select '3月' as monthname ,'3'as months
union all
select '4月' as monthname ,'4'as months
union all
select '5月' as monthname ,'5'as months
union all
select '6月' as monthname ,'6'as months
union all
select '7月' as monthname ,'7'as months
union all
select '8月' as monthname ,'8'as months
union all
select '9月' as monthname ,'9'as months
union all
select '10月' as monthname ,'10'as months
union all
select '11月' as monthname ,'11'as months
union all
select '12月' as monthname ,'12'as months)a
left outer join(
select DATEPART( month,dtime) as months ,
sum(case city when '长沙' then 1 else 0 end) as '长沙',
sum(case city when '湘潭' then 1 else 0 end) as '湘潭',
sum(case city when '株洲' then 1 else 0 end) as '株洲',
sum(case city when '衡阳' then 1 else 0 end) as '衡阳',
sum(case city when '郴州' then 1 else 0 end) as '郴州',
sum(case city when '常德' then 1 else 0 end) as '常德',
sum(case city when '益阳' then 1 else 0 end) as '益阳',
sum(case city when '娄底' then 1 else 0 end) as '娄底',
sum(case city when '邵阳' then 1 else 0 end) as '邵阳',
sum(case city when '岳阳' then 1 else 0 end) as '岳阳',
sum(case city when '自治州' then 1 else 0 end) as '自治州',
sum(case city when '张家界' then 1 else 0 end) as '张家界',
sum(case city when '怀化' then 1 else 0 end) as '怀化',
sum(case city when '永州' then 1 else 0 end) as '永州',
sum(1) as '全省'
from @t1 group by DATEPART( month,dtime)) b on b.months = a.months
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc
SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4
UNION
SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;SELECT * FROM t_change_lc;SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;--行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a.card_code,
SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4
FROM t_change_lc a
GROUP BY a.card_code
ORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code,
t.rn q,
decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM t_change_cl a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
ORDER BY 1, 2;--行列转换 行转列 合并
DROP TABLE t_change_lc_comma;
CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc; SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;--行列转换 列转行 分割
DROP TABLE t_change_cl_comma;
CREATE TABLE t_change_cl_comma AS
SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q
FROM (SELECT a.card_code,
a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM t_change_lc_comma a) t1
START WITH t1.rn = 1
CONNECT BY t1.card_code = PRIOR t1.card_code
AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code,
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM t_change_cl_comma a,
(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) > 0) t
ORDER BY 1, 2;
sum(decode(acceptbranch,'2',count1,'0')) 长沙,
sum(decode(acceptbranch,'3',count1,'0')) 湘潭,
sum(decode(acceptbranch,'4',count1,'0')) 株洲,
sum(decode(acceptbranch,'5',count1,'0')) 衡阳,
sum(decode(acceptbranch,'6',count1,'0')) 郴州,
sum(decode(acceptbranch,'7',count1,'0')) 常德,
sum(decode(acceptbranch,'8',count1,'0')) 益阳,
sum(decode(acceptbranch,'9',count1,'0')) 娄底,
sum(decode(acceptbranch,'10',count1,'0')) 邵阳,
sum(decode(acceptbranch,'11',count1,'0')) 岳阳,
sum(decode(acceptbranch,'12',count1,'0')) 吉首,
sum(decode(acceptbranch,'13',count1,'0')) 张家界,
sum(decode(acceptbranch,'14',count1,'0')) 怀化,
sum(decode(acceptbranch,'15',count1,'0')) 永州,
sum(count1) 全省
from (select count(1) as count1,to_char(planday,'yyyy') as year1,to_char(planday,'MM') month1,acceptbranch from newworksheet
where ((endResults='计划解决' and planstatus='0') or planstatus!='0') and status!='5' and status!='7' and to_char(planday,'yyyy')='2009' and (to_char(planday,'MM')='07' or to_char(planday,'MM')='08' or to_char(planday,'MM')='09')
group by to_char(planday,'yyyy'),to_char(planday,'MM'),acceptbranch) t
group by year1,month1解决了,谢谢