62,041
社区成员
发帖
与我相关
我的任务
分享table行与列转换问题？
例如：
表1：
部门 季度 销售额
1 1 100
1 2 105
1 3 98
1 4 87
转换后成：
季度1 季度2 季度3 季度4
部门1 100 105 98 87
求代码：？
表1：
部门 季度 销售额
1 1 100
1 2 105
1 3 98
1 4 87
转换后成：
季度1 季度2 季度3 季度4
部门1 100 105 98 87
求代码：？
...全文
请发表友善的回复…
发表回复
GingerbreadMan 2008-06-16
- 打赏
- 举报
这其实是交叉表的生成。
假设表已经存在datatable中。
用以下思路编写代码：
1.生成一个新的空datatable，添加“部门”列。
2.遍历原datatable每行，将“季度”中的数值按照你的格式（即“季度n"）不重复地添加为新列。
3.再次遍历原表，将“销售额”的数据按照对应关系添加进新的datatable中。
假设表已经存在datatable中。
用以下思路编写代码：
1.生成一个新的空datatable，添加“部门”列。
2.遍历原datatable每行，将“季度”中的数值按照你的格式（即“季度n"）不重复地添加为新列。
3.再次遍历原表，将“销售额”的数据按照对应关系添加进新的datatable中。
shoushii 2008-06-16
- 打赏
- 举报
/*
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 行）
*/shoushii 2008-06-16
- 打赏
- 举报
/*
部门 季度1 季度2 季度3 季度4
--------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
部门1 100.00 105.00 98.00 87.00
（所影响的行数为 1 行）
*/shoushii 2008-06-16
- 打赏
- 举报
/*
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
|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 |
| | | | | | | | | | |
+--------+----------+--------+--------+------------+-------+----------+----------+----------+----------+
*/
--测试完毕，还是全英文比较美观
「已注销」 2008-06-16
- 打赏
- 举报
类似这样
「已注销」 2008-06-16
- 打赏
- 举报
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
.
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
.
shoushii 2008-06-16
- 打赏
- 举报
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
shoushii 2008-06-16
- 打赏
- 举报
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
shoushii 2008-06-16
- 打赏
- 举报
/*
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
*/shoushii 2008-06-16
- 打赏
- 举报
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 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+shoushii 2008-06-16
- 打赏
- 举报
/*
+----------------+---------------------+--------------------+--------------------+---------------------+
| 部门 | 季度1 | 季度2 | 季度3 | 季度4 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
|部门1 |100 |105 |98 |87 |
| | | | | |
+----------------+---------------------+--------------------+--------------------+---------------------+
*/
xxoo2007 2008-06-16
- 打赏
- 举报
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 部门
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 部门
hubblebubblepig 2008-06-16
- 打赏
- 举报
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.p
p----部门
e----销售额
j----季度
x----表
t----别名
(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.p
p----部门
e----销售额
j----季度
x----表
t----别名
shoushii 2008-06-16
- 打赏
- 举报
[Quote=引用 3 楼 conan304 的回复:]
SQL codedeclare @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,87
select * from @t
select 部门='部门'+cast(部门 as varchar),
'季度1'=sum((case 季度 when 1 then 销售额 end)),
'季度2'=sum((case 季度 when 2 then 销售额 end)),
'季度3'=sum((case 季度 when 3 then 销售额 end)),
…
[/Quote]
用的是SQL PLUS么？我刚打完发现你就在了。
这个结果是从SQL PLUS复制的吧？SQL 2000/2005支持吗？
SQL codedeclare @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,87
select * from @t
select 部门='部门'+cast(部门 as varchar),
'季度1'=sum((case 季度 when 1 then 销售额 end)),
'季度2'=sum((case 季度 when 2 then 销售额 end)),
'季度3'=sum((case 季度 when 3 then 销售额 end)),
…
[/Quote]
用的是SQL PLUS么？我刚打完发现你就在了。
这个结果是从SQL PLUS复制的吧？SQL 2000/2005支持吗？
shoushii 2008-06-16
- 打赏
- 举报
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
conan304 2008-06-16
- 打赏
- 举报
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,87
select * from @t
select 部门='部门'+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 操作消除了空值。
*/hubblebubblepig 2008-06-16
- 打赏
- 举报
[Quote=引用 1 楼 hubblebubblepig 的回复:]
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 x
p----部门
e----销售额
j----季度
x----表
[/Quote]
当部门为多个时会出错
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 x
p----部门
e----销售额
j----季度
x----表
[/Quote]
当部门为多个时会出错
hubblebubblepig 2008-06-16
- 打赏
- 举报
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 x
p----部门
e----销售额
j----季度
x----表
(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 x
p----部门
e----销售额
j----季度
x----表
