select distinct p, (select e from x where j=1 and p=x.p) as jidu1, (select e from x where j=2 and p=x.p) as jidu2, (select e from x where j=3 and p=x.p) as jidu3, (select e from x where j=4 and p=x.p) as jidu4 from xp----部门 e----销售额 j----季度 x----表
declare @t table(部门 int,季度 int,销售额 numeric(12,4)) insert @t select 1,1,100 union all select 1,2,105 union all select 1,3,98 union all select 1,4,87select * from @tselect 部门='部门'+cast(部门 as varchar), '季度1'=sum((case 季度 when 1 then 销售额 end)), '季度2'=sum((case 季度 when 2 then 销售额 end)), '季度3'=sum((case 季度 when 3 then 销售额 end)), '季度4'=sum((case 季度 when 4 then 销售额 end)) from @t group by 部门 /*（所影响的行数为 4 行）部门 季度 销售额 ----------- ----------- -------------- 1 1 100.0000 1 2 105.0000 1 3 98.0000 1 4 87.0000（所影响的行数为 4 行）部门 季度1 季度2 季度3 季度4 -------- ------- -------------- ---------------- ---------------- 部门1 100.0000 105.0000 98.0000 87.0000（所影响的行数为 1 行）警告: 聚合或其它 SET 操作消除了空值。 */
create table sales ( 部门 int , 季度 int, 销售额 decimal(18,2) )insert into sales values(1,1,100) insert into sales values(1,2,105) insert into sales values(1,3,98) insert into sales values(1,4,87) --...select '部门'+convert(nvarchar(5),部门) as '部门', sum(case when 季度=1 then 销售额 else 0 end) 季度1, sum(case when 季度=2 then 销售额 else 0 end) 季度2, sum(case when 季度=3 then 销售额 else 0 end) 季度3, sum(case when 季度=4 then 销售额 else 0 end) 季度4 from sales group by 部门 drop table sales
select t.p, sum(jidu1) as jidu1, sum(jidu2) as jidu2, sum(jidu3) as jidu3, sum(jidu4) as jidu4 from (select p, jidu1 = case j when 1 then e else 0 end, jidu2 = case j when 2 then e else 0 end, jidu3 = case j when 3 then e else 0 end, jidu4 = case j when 4 then e else 0 end from x) as t group by t.pp----部门 e----销售额 j----季度 x----表 t----别名
select 部门, sum(case when 季度=1 then 销售额 else 0 end ) as 一季度 , sum(case when 季度=2 then 销售额 else 0 end ) as 二季度, sum(case when 季度=3 then 销售额 else 0 end ) as 三季度, sum(case when 季度=4 then 销售额 else 0 end ) as 四季度 from c group by 部门
create table sales ( 部门 int , 季度 int, 销售额 decimal(18,2) )insert into sales values(1,1,100) insert into sales values(1,2,105) insert into sales values(1,3,98) insert into sales values(1,4,87) --...select '部门'+convert(nvarchar(5),部门) as '部门', sum(case when 季度=1 then 销售额 else 0 end) 季度1, sum(case when 季度=2 then 销售额 else 0 end) 季度2, sum(case when 季度=3 then 销售额 else 0 end) 季度3, sum(case when 季度=4 then 销售额 else 0 end) 季度4 from sales group by 部门 drop table sales+----------------+---------------------+--------------------+--------------------+---------------------+ | 部门 | 季度1 | 季度2 | 季度3 | 季度4 | | | | | | | +----------------+---------------------+--------------------+--------------------+---------------------+ |部门1 |100 |105 |98 |87 | | | | | | | +----------------+---------------------+--------------------+--------------------+---------------------+
select col, max(case [month] when 1 then amount else 0 end) 'mon1', max(case [month] when 2 then amount else 0 end) 'mon2', max(case [month] when 3 then amount else 0 end) 'mon3', max(case [month] when 4 then amount else 0 end) 'mon4', max(case [month] when 5 then amount else 0 end) 'mon5', max(case [month] when 6 then amount else 0 end) 'mon6', max(case [month] when 7 then amount else 0 end) 'mon7', max(case [month] when 8 then amount else 0 end) 'mon8', max(case [month] when 9 then amount else 0 end) 'mon9', max(case [month] when 10 then amount else 0 end) 'mon10', max(case [month] when 11 then amount else 0 end) 'mon11', max(case [month] when 12 then amount else 0 end) 'mon12' from table .
(select e from x where j=1 and p=x.p) as jidu1,
(select e from x where j=2 and p=x.p) as jidu2,
(select e from x where j=3 and p=x.p) as jidu3,
(select e from x where j=4 and p=x.p) as jidu4 from xp----部门
e----销售额
j----季度
x----表
insert @t select 1,1,100
union all select 1,2,105
union all select 1,3,98
union all select 1,4,87select * from @tselect 部门='部门'+cast(部门 as varchar),
'季度1'=sum((case 季度 when 1 then 销售额 end)),
'季度2'=sum((case 季度 when 2 then 销售额 end)),
'季度3'=sum((case 季度 when 3 then 销售额 end)),
'季度4'=sum((case 季度 when 4 then 销售额 end))
from @t
group by 部门
/*（所影响的行数为 4 行）部门 季度 销售额
----------- ----------- --------------
1 1 100.0000
1 2 105.0000
1 3 98.0000
1 4 87.0000（所影响的行数为 4 行）部门 季度1 季度2 季度3 季度4
-------- ------- -------------- ---------------- ----------------
部门1 100.0000 105.0000 98.0000 87.0000（所影响的行数为 1 行）警告: 聚合或其它 SET 操作消除了空值。
*/
(
部门 int ,
季度 int,
销售额 decimal(18,2)
)insert into sales values(1,1,100)
insert into sales values(1,2,105)
insert into sales values(1,3,98)
insert into sales values(1,4,87)
--...select
'部门'+convert(nvarchar(5),部门) as '部门',
sum(case when 季度=1 then 销售额 else 0 end) 季度1,
sum(case when 季度=2 then 销售额 else 0 end) 季度2,
sum(case when 季度=3 then 销售额 else 0 end) 季度3,
sum(case when 季度=4 then 销售额 else 0 end) 季度4
from sales group by 部门
drop table sales
(select p,
jidu1 = case j when 1 then e else 0 end,
jidu2 = case j when 2 then e else 0 end,
jidu3 = case j when 3 then e else 0 end,
jidu4 = case j when 4 then e else 0 end from x) as t group by t.pp----部门
e----销售额
j----季度
x----表
t----别名
sum(case when 季度=1 then 销售额 else 0 end ) as 一季度 ,
sum(case when 季度=2 then 销售额 else 0 end ) as 二季度,
sum(case when 季度=3 then 销售额 else 0 end ) as 三季度,
sum(case when 季度=4 then 销售额 else 0 end ) as 四季度
from c
group by 部门
(
部门 int ,
季度 int,
销售额 decimal(18,2)
)insert into sales values(1,1,100)
insert into sales values(1,2,105)
insert into sales values(1,3,98)
insert into sales values(1,4,87)
--...select
'部门'+convert(nvarchar(5),部门) as '部门',
sum(case when 季度=1 then 销售额 else 0 end) 季度1,
sum(case when 季度=2 then 销售额 else 0 end) 季度2,
sum(case when 季度=3 then 销售额 else 0 end) 季度3,
sum(case when 季度=4 then 销售额 else 0 end) 季度4
from sales group by 部门
drop table sales+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
max(case [month] when 1 then amount else 0 end) 'mon1',
max(case [month] when 2 then amount else 0 end) 'mon2',
max(case [month] when 3 then amount else 0 end) 'mon3',
max(case [month] when 4 then amount else 0 end) 'mon4',
max(case [month] when 5 then amount else 0 end) 'mon5',
max(case [month] when 6 then amount else 0 end) 'mon6',
max(case [month] when 7 then amount else 0 end) 'mon7',
max(case [month] when 8 then amount else 0 end) 'mon8',
max(case [month] when 9 then amount else 0 end) 'mon9',
max(case [month] when 10 then amount else 0 end) 'mon10',
max(case [month] when 11 then amount else 0 end) 'mon11',
max(case [month] when 12 then amount else 0 end) 'mon12'
from table
.
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|ProductI|ProductNam|Supplier|Category|QuantityPerU|UnitPri|UnitsInSto|UnitsOnOrd|ReorderLev|Discontinu|
| D | e | ID | ID | nit | ce | ck | er | el | ed |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |10 boxes x | | | | | |
|1 |Chai |1 |1 |20 bags |18 |39 |0 |10 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |24 - 12 oz | | | | | |
|2 |Chang |1 |1 |bottles |19 |17 |40 |25 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| |Aniseed | | |12 - 550 ml | | | | | |
|3 |Syrup |1 |2 |bottles |10 |13 |70 |25 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Chef | | | | | | | | |
|4 |Anton's |2 |2 |48 - 6 oz |22 |53 |0 |0 |False |
| |Cajun | | |jars | | | | | |
| |Seasoning | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Chef | | | | | | | | |
|5 |Anton's |2 |2 |36 boxes |21.35 |0 |0 |0 |True |
| |Gumbo Mix | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|--------|----------|--------|--------|------------|-------|----------|----------|----------|----------|
| |Grandma's | | | | | | | | |
|6 |Boysenberr|3 |2 |12 - 8 oz |25 |120 |0 |25 |False |
| |y Spread | | |jars | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Uncle | | | | | | | | |
| |Bob's | | | | | | | | |
|7 |Organic |3 |7 |12 - 1 lb |30 |15 |0 |10 |False |
| |Dried | | |pkgs. | | | | | |
| |Pears | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | | | | | | | |
| | | | | | | | | | |
| |Northwoods| | | | | | | | |
|8 |Cranberry |3 |2 |12 - 12 oz |40 |6 |0 |0 |False |
| |Sauce | | |jars | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|--------|Mishi Kobe|--------|--------|18 - 500 g |-------|----------|----------|----------|----------|
|9 |Niku |4 |6 |pkgs. |97 |29 |0 |0 |True |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
| | | | |12 - 200 ml | | | | | |
|10 |Ikura |4 |8 |jars |31 |31 |0 |0 |False |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
*/--测试完毕，还是全英文比较美观
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------
1 Chai 1 1 10 boxes x 20 bags 18.0000 39 0 10 0
2 Chang 1 1 24 - 12 oz bottles 19.0000 17 40 25 0
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.0000 13 70 25 0
4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22.0000 53 0 0 0
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.3500 0 0 0 1
6 Grandma's Boysenberry Spread 3 2 12 - 8 oz jars 25.0000 120 0 25 0
7 Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. 30.0000 15 0 10 0
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.0000 6 0 0 0
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.0000 29 0 0 1
10 Ikura 4 8 12 - 200 ml jars 31.0000 31 0 0 0（所影响的行数为 10 行）
*/
假设表已经存在datatable中。
用以下思路编写代码：
1.生成一个新的空datatable，添加“部门”列。
2.遍历原datatable每行，将“季度”中的数值按照你的格式（即“季度n"）不重复地添加为新列。
3.再次遍历原表，将“销售额”的数据按照对应关系添加进新的datatable中。