查询数据(行到列的数据) [英] Querying Data (data from row to column)
本文介绍了查询数据(行到列的数据)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当前显示数据为:
Current showing data are:
col1 col2 col3 col4
1 AA A001 300
2 AA B001 200
3 AA C001 450
4 BB A001 230
5 BB E001 300
6 CC A001 200
7 CC A002 300
... ... ... ...
... ... ... ...
... ... ... ...
结果将是:
The result will be:
A001 A002 B001 C001 E001 ...
AA 300 0 200 450 0 ...
BB 230 0 0 0 300 ...
CC 200 300 0 0 0 ...
... ... ... ... ... ... ...
... ... ... ... ... ... ...
我有上述问题.所以,请帮忙!
附注:AA,BB,CC ...以及A001,A002,B0001 ...都可以种植.我希望结果是动态增加行和列".
I have above problem. So, pls help!
P.S: AA,BB,CC,... and also A001,A002,B0001,... can be grown. I wish the result be "dynamically increase rows and columns".
推荐答案
有几种方法.
在SQL Server中透视数据 [
There are couple of ways.
Pivoting data in SQL Server[^]
create table #temptab
(
col1 int ,
col2 varchar(10),
col3 varchar(10),
col4 int
)
insert into #temptab
select 1, 'AA', 'A001', 300
insert into #temptab
select 2, 'AA', 'B001', 200
insert into #temptab
select 3, 'AA', 'C001', 450
insert into #temptab
select 4, 'BB', 'A001', 230
insert into #temptab
select 5, 'BB', 'E001', 300
insert into #temptab
select 6, 'CC', 'A001', 200
insert into #temptab
select 7, 'CC', 'A002', 300
select COL2, ISNULL([A001],0),ISNULL([A002],0),ISNULL([B001],0),ISNULL([C001],0),ISNULL([E001],0) from
(select col2, col3, col4
from #temptab) tab1
PIVOT (max(col4) FOR COL3 IN([A001],[A002],[B001],[C001],[E001]) ) TAB2
我认为这应该可以给您一个清晰的画面....
I think this should give u a clear picture....
您需要创建一个PIVOT表:MSDN有说明和一个小示例: ^ ]
You need to create a PIVOT table: MSDN has instructions and a small example: Pivot table[^]
这篇关于查询数据(行到列的数据)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文